VBA find cell of closest value

Question

I have an excel file that looks like:

12123    51212
12123.5  45832
12124    37656
12124.5  32987
12125    42445

and so on, where column A is always 0.5 increasing and column B has a certain output.

Now I have a specific value in cell E2, say 12124,23 and I want a VBA code to return, in this case, that the best matching value is in cell A3, because I need this cell location in further code, I don't need the corresponding value in column B. I don't know how to start, however. The file can be up to 30000 rows big.

I'd only like to know first which method to use, then I will try to write the code myself of course :)

JV

Answer 1

You don't have to use VBA for your problem, Excel will do it perfectly fine!

Try this

=vlookup(E2;A:A;2;true)

and for what you are trying to do, you HAVE TO sort your A column in an ascending fashion, or else you will get an error!

And if you do need that in VBA,

a simple for+if structure with a test like this

    Function pr24(ByVal Value_To_Match As Double) As Range


For i = 2 To ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    If Cells(i, 1) > Value_To_Match Then
        If Abs(Cells(i - 1, 1) - Value_To_Match) >= Abs(Cells(i, 1) - Value_To_Match) Then
            pr24 = Range(Cells(i, 1))
        Else
            pr24 = Range(Cells(i - 1, 1))
        End If

        Exit For
    Else

    End If
Next i



End Function

or you can use the worksheet function Vlookup

Application.WorksheetFunction.VLOOKUP()