Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get the row number that matches my value
I would like to return the row number in which there is a match to a my
variable. In this case a is the value I am looking for and its in column a of Sheets("Sec1").Range("A1:A500") How should I go about this? Am i going in the right direction by using Cells.Find? Bruce Sub myDateRow() a = Format(Sheets("Shares").Range("D9"), "yyyymmdd") With Sheets("Sec1").Range("A1:A500") Range("A1").Select Cells.Find(What:=""" & a & """, After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get the row number that matches my value
try
Sub myDateRow() Dim a As Date a = Sheets("Shares").Range("D9") With Sheets("Sec1").Range("A1:A500") .Range("A500").Select .Cells.Find(What:=a, After:=ActiveCell, _ LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False).Activate End With End Sub "Bruce" wrote: I would like to return the row number in which there is a match to a my variable. In this case a is the value I am looking for and its in column a of Sheets("Sec1").Range("A1:A500") How should I go about this? Am i going in the right direction by using Cells.Find? Bruce Sub myDateRow() a = Format(Sheets("Shares").Range("D9"), "yyyymmdd") With Sheets("Sec1").Range("A1:A500") Range("A1").Select Cells.Find(What:=""" & a & """, After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate End With End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get the row number that matches my value
Try this:
Function GetRow(sText) As Long Dim lRow As Long lRow = 0 On Error Resume Next lRow = Sheets("Sheet1").Range("A1:A500").Find(What:=sText , _ LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False).Row On Error GoTo 0 GetRow = lRow End Function Tim "Bruce" wrote in message ... I would like to return the row number in which there is a match to a my variable. In this case a is the value I am looking for and its in column a of Sheets("Sec1").Range("A1:A500") How should I go about this? Am i going in the right direction by using Cells.Find? Bruce Sub myDateRow() a = Format(Sheets("Shares").Range("D9"), "yyyymmdd") With Sheets("Sec1").Range("A1:A500") Range("A1").Select Cells.Find(What:=""" & a & """, After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add number before text that matches criteria | Excel Worksheet Functions | |||
Counting number of times data matches | Excel Worksheet Functions | |||
Change fmt of a cell if number matches any in a range of cells? | Excel Worksheet Functions | |||
Excel filter no longer calculates the number of matches | Excel Discussion (Misc queries) | |||
How do I count number of cels the matches 2 conditions ? | Excel Worksheet Functions |