Use Contents of Cell in a Find

[vlswanson]

Hi,

I'm hung up on something that seems so simple! As part of a larger process, I want to find the row that contains a value in column A that corresponds with the information in another cell. I did some searching and found this code:

Public Function GetRowNum(SheetName As String, SearchVal As String) As Long
GetRowNum = Sheets(SheetName).Columns(1).Cells.Find(SearchVal).Row
End Function

Sub FindRow()
Dim MyRow As Long
MyRow = GetRowNum("Db", "3650")
Range("A" & MyRow).Select
End Sub

This works great if one manually enters the value being searched for (the "3650" above), however I want to use the contents of cell B2 where the "3650" is. I've played around with this but haven't found the right combination I'd appreciate your help.

 

[JoeMo]

If cell B2 contains "3650", try removing the quote marks - just enter the number 3650 in B2 then change this line:

MyRow = GetRowNum("Db", "3650")

to this:

MyRow = GetRowNum("Db", [B2])

 

[Fluff]

How about

Sub test()
   Dim Fnd As Range
   Dim MyRow As Long
   Set Fnd = Range("A:A").Find(Range("B2").Value, , , xlWhole, , , False, , False)
   If Not Fnd Is Nothing Then MyRow = Fnd.Row
End Sub

 

[vlswanson]

If cell B2 contains "3650", try removing the quote marks - just enter the number 3650 in B2 then change this line:

MyRow = GetRowNum("Db", "3650")

to this:

MyRow = GetRowNum("Db", [B2])

Unfortunately that didn't work...got Runtime Error 91: Object variable or With block variable not set

 

[JoeMo]

Unfortunately that didn't work...got Runtime Error 91: Object variable or With block variable not set

Does cell B2 of sheet Db have just the number 3650 in it? If yes, the error suggests that 3650 is not found in any cell in col A of sheet Db, in part or in whole. Can you post a sample of your data in col A including a cell that you think has 3650 in it?

 

[vlswanson]

Does cell B2 of sheet Db have just the number 3650 in it? If yes, the error suggests that 3650 is not found in any cell in col A of sheet Db, in part or in whole. Can you post a sample of your data in col A including a cell that you think has 3650 in it?

Apologies...thought I had edited my second post. Both solutions did work....THANK YOU!

 

[Fluff]

Glad to help & thanks for the feedback