Hi,
Try this which assumes data is in colums A to C and there is a header
row.
It checks if value to be matched is within limits of data and returns 0 if
not; otherwise returns the row number of match .

Sub testMatch()
MsgBox FindRow(3001)
End Sub



Function FindRow(ByVal MatchVal As Integer)
Dim Lastrow As Integer, mRow As Integer, Matchrng As Range

Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set Matchrng = Range("b2:B" & Lastrow)

' Check if value is within limits of table
If MatchVal Application.Max(Range("C2:C" & Lastrow)) _
Or MatchVal < Application.Min(Range("B2:B" & Lastrow)) Then
MsgBox MatchVal & " is out of range"
FindRow = 0 ' Return row number of 0
Exit Function
Else
FindRow = Application.Match(MatchVal, Matchrng)+ 1
End If

End Function


HTH


"Soniya" wrote:

Hi all,

I have data something like:
type from to
a 1000 1500
b 1501 2500
c 2501 2750

i want to search for a number and find out in which row it comes

for eg: if i type 1750 it comes in b (starting 1501 and ending 2500)

how can i acheive this using VBA?

Thanks