Search in row

[Soniya]

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

 

[Guest]

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]

thanks for your reply..

it works when the number is small and returns an error when the number
is big

if the number is in 1000 etc it worked. but when i used 400000 etc it
says overflow?

 

[Guest]

Change Function FindRow(ByVal MatchVal As Integer) to

Function FindRow(ByVal MatchVal As Long)

 

[Don Guillett]

Sub findunm()
MsgBox Cells(Application.Match(1700, Columns(4)), "c")
'or if 1700 in b2
MsgBox Cells(Application.Match(Cells(2, 2), Columns(4)), "c")
End Sub

or a built in function
=INDIRECT("c"&MATCH(B2,D323)+2)