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.

 

[Norie]

What do you want to happen when a match isn't found?

Dim lc As Long
Dim Res As Variant
    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
        Res = Application.VLookup(ActiveSheet.Name & cel2, Sheets("mappings").Range("$G:$H"), 2, False)
        If IsError(Res) Then
            cel2.Offset(1,0).Value = ""
        Else
            cel2.Offset(1, 0) = Res
    Next

 

[matt767]

@Norie Just leave the cell blank

 

[matt767]

@Norie The code worked for the non-formatted cells but not the formatted ones.

 

[Norie]

How did it not work?

 

[matt767]

@Norie it left the cells blank. Most of my sheet is bolded and/or color filled and it won't match the lookup range which is non-formatted.

 

[matt767]

@Norie it worked only for the bolded ones not the bolded/color filled ones.

 

[matt767]

@Norie sorry again...the reason was the font color of the bolded/color filled cells was white and it was transferring the white font into the white target cells...

 

[Norie]

That shouldn't happen as the code I posted only puts values in cells, it doesn't copy formatting.

 

[matt767]

@Norie sorry the destination cells were already formatted to contain white text. I totally forgot.