Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search in row
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search in row
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search in row
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search in row
Change Function FindRow(ByVal MatchVal As Integer) to
Function FindRow(ByVal MatchVal As Long) "Soniya" wrote: 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search in row
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,D3:D23)+2) -- Don Guillett SalesAid Software "Soniya" wrote in message ups.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Two search category & two search terms | Excel Discussion (Misc queries) | |||
file search or search files | Excel Discussion (Misc queries) | |||
Functions (search within search result) reply to this please | Excel Worksheet Functions | |||
How do I search excel spreadsheets using multiple search criteria. | Excel Worksheet Functions | |||
Create a search Field within a worksheet to search command buttons | Excel Programming |