Solved: Find row above and below searchvalue, VBA

[Ago]

Hi.

Lets say I have a column with the following values.

A1 2
A2 4
A3 6
A4 8
A5 10

And the user inputs "5".
How can I write a code that gives me two variables called for example "above" and "below" with the rownumbers 2 and 3.
I know for sure the value the user inputs does not excist (all values in the column have decimals).

[Simon Lloyd]

You dont need code just some vlookups, example:
=vlookup(B1,A:A,1,TRUE)
usually you'd use FALSE as the type of search but in this case using TRUE will find the nearest value without going over the specified value, modifying one or two of these would work for you :)

[Ago]

I do need the code.
It will be part of a big code.


Edit: but thanks for the alternative

[mdmackillop]

Assuming your data is in ascending order

Sub Test()
Dim x As Single, y As Long
Dim r As Range

Set r = Range("Data")
x = InputBox("Enter Value")
y = Application.Match(x, Range("Data"), 1)

MsgBox r(y) & ", " & r(y + 1)

End Sub

[Ago]

Assuming your data is in ascending order

Sub Test()
Dim x As Single, y As Long
Dim r As Range

Set r = Range("Data")
x = InputBox("Enter Value")
y = Application.Match(x, Range("Data"), 1)

MsgBox r(y) & ", " & r(y + 1)

End Sub


:banghead:
No, it was in decending order. Never thought about that.
Just wrote the post on my phone last night.

But thatnks for the code I managed to get it working.