Error when match is not found when using Find method

user_14969947 0
Viewed 27

I know this probably has extensive coverage, and I've tried a few of those, but still can't get it to work:

        Dim d As Range
        For Each d In orng
            Dim val As Range
            Set val = adws.Range("O2:O" & lrw).Find(d.Value, , xlValues, xlWhole).Offset(0, 26)
            
            If val Is Nothing Then
                MsgBox d.Value & " not found."
                End
            Else
                d.Offset(0, 12) = val.Value
            End If
        Next

Code works fine as long as the function finds a value, but gets error on the first one that it does not. Setting the val variable and running the Find function on the same line, I got from other forums. But this doesn't seem to work at all. Find still gets confused when it can't find a value.

Thanks in advance.

find vba excel
1 Answers

The .Offset(0, 26) is problematic. Don't chain any calls to the Find call, instead only Offset after you've ensured the Find succeeded.

Also consider using another variable name than Val as that shadows the Val function.

Dim rng as Range
Set rng = adws.Range("O2:O" & lrw).Find(d.Value, , xlValues, xlWhole)
           
If rng Is Nothing Then
    MsgBox d.Value & " not found."
    End
Else
    d.Offset(0, 12).Value = rng.Offset(0, 26).Value ' Offset here since Find succeeded
End If
user_92458530
Related