Hi,
I've been testing a piece of code but can only get it to work when I set my variables to string values and look up an alpha character. When I change my variable to integer and look for a number, I get a "Type Mismatch Error 13". It seems to have something to do with the "evaluate" formula, but not sure. Your suggestions are welcome.
Below are two versions of my code. FindABC() works, while Find123() gets an error. A test worksheet with these routines are also attached.
Many thanks!
Cesar
Private Sub FindABC()
'This routine finds cell address C3 using match in column A (alpha)
Dim FindWhat As String
Dim CAddress As String
Dim LastRow As Long
FindWhat = "C"
Sheets("sheet1").Activate
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
MsgBox "Last Row is " & LastRow
CAddress = Evaluate("=CELL(""address"",INDEX(C1:C" & LastRow & ",MATCH(""" & FindWhat & """,A1:A" & LastRow & ",0)))")
MsgBox "Cell address is " & CAddress
End Sub
Private Sub Find123()
'This routine should finds cell address C3 using match in column B (numeric), but comes up with a Type Mismatch Error 13
Dim FindWhat As Integer
Dim CAddress As String
Dim LastRow As Long
FindWhat = "3"
Sheets("sheet1").Activate
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
MsgBox "Last Row is " & LastRow
CAddress = Evaluate("=CELL(""address"",INDEX(C1:C" & LastRow & ",MATCH(""" & FindWhat & """,B1:B" & LastRow & ",0)))")
MsgBox "Cell address is " & CAddress
End Sub
Bookmarks