CODEWITHSUNDEEP
excelvbafind
Carlos Andres
Carlos Andres

Reputation: 99

Error when match is not found when using Find method

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.

Upvotes: 1

Views: 49

Answers (1)

BigBen
BigBen

Reputation: 50162

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

Upvotes: 2

Related Questions