PDA

View Full Version : Solved: Find row above and below searchvalue, VBA



Ago
04-09-2013, 10:39 AM
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
04-09-2013, 10:42 AM
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
04-09-2013, 10:52 AM
I do need the code.
It will be part of a big code.


Edit: but thanks for the alternative

mdmackillop
04-09-2013, 11:09 AM
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
04-09-2013, 10:48 PM
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.