Unable To Get VLookup Property Error Despite Numerous (Attempted) Workarounds

[matt767]

code excerpt:

    Dim lc As Long
    lc = Cells(1, columns.Count).End(xlToLeft).column
    Dim cel2 As Range
    Dim srchrng As Range
    Set srchrng = Range(Cells(1, 39), Cells(1, lc))
    For Each cel2 In srchrng
    cel2.Offset(1, 0) = Application.WorksheetFunction.VLookup(ActiveSheet.Name & cel2, Sheets("mappings").Range("$G:$H"), 2, False)
    Next

Every time vlookup can't find a match, it returns the "Run-time error '1004': Unable to get the VLookup property of the WorksheetFunction class" error and stops the code. I have tried error handling using On Error Resume Next and On Error GoTo but when I do the vlookup doesn't return any values (but no error). I have also tried using an If Iserror(vlookup(...)) = True Then cel2.offset(1,0).value = "" Else cel2... = vlookup(...) and that also returns the same error when there is no match.

A separate issue is when the formatting of the cel2 range differs from the (non-formatted) range Sheets("mappings").Range("$G:$H") - vlookup again will not find a match. I have tried using cel2.text and cel2.value to no avail. I would like to be able to keep the formatting of my sheet.

Any help is appreciated. Thanks.

 

[matt767]

ty