Use row number in spreadsheet as variable in macro
 

I am using FIND in a macro to select a specific piece of data in a
speadsheet. I then wish to select that row of data to copy and paste
elsewhere. I need to be able to assign the row number to a variable.

Specifically: In a form we enter a sample id number. When the
"Continue" command button is pressed, a macro is invoked that selects a
worksheet that contains many lab id numbers - each lab id number is in
its own row with a bunch of other information pertaining to that lab id
number on the same row.

In the macro, I want to be able to assign the row number where the lab
id is found to a variable.

Let's call the variable strRowNumber. How can I syntactically say

strRowNumber = the row number where the lab id is found

so that the variable contains the row number where the lab id was found
on the workshhet??

Any help would be greatly appreciated. I'm driving myself nuts.

Thanks, Capt. Bluefin



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Use row number in spreadsheet as variable in macro
 

Try something like this

Sub Test()
Dim FoundCell As Range
Dim strRowNumber As Long
With Range("A:A")
Set FoundCell = .find(What:="112", LookAt:=xlWhole)
If FoundCell Is Nothing Then
MsgBox "nothing found"
Else
strRowNumber = FoundCell.Row
MsgBox strRowNumber
End If
End With
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"captbluefin" wrote in message ...
I am using FIND in a macro to select a specific piece of data in a
speadsheet. I then wish to select that row of data to copy and paste
elsewhere. I need to be able to assign the row number to a variable.

Specifically: In a form we enter a sample id number. When the
"Continue" command button is pressed, a macro is invoked that selects a
worksheet that contains many lab id numbers - each lab id number is in
its own row with a bunch of other information pertaining to that lab id
number on the same row.

In the macro, I want to be able to assign the row number where the lab
id is found to a variable.

Let's call the variable strRowNumber. How can I syntactically say

strRowNumber = the row number where the lab id is found

so that the variable contains the row number where the lab id was found
on the workshhet??

Any help would be greatly appreciated. I'm driving myself nuts.

Thanks, Capt. Bluefin



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/