CODEWITHSUNDEEP
vbaexcel
Helloguys
Helloguys

Reputation: 289

Excel-VBA Range.Find method error handling

I'm using the range.find method to locate a cell with specific value in the first row. When there's a match, the code works fine. When there's no match, the code throws error as shown in screenshot below.

Is it normal that we need error handling for this method? I thought it'd just return Null or Nothing. Thank you for your help!

enter image description here

Upvotes: 0

Views: 6728

Answers (2)

Виктор Я
Виктор Я

Reputation: 3

Sub aa() Dim res As Variant

On Error Resume Next
With ActiveWorkbook.Sheets(1).Range("A2:A5")
    Set res = .Find("4")
    If res = Null Then
        Debug.Print "null"
    Else: Debug.Print res.Value
    End If
End With

End Sub

Upvotes: -1

Mahhdy
Mahhdy

Reputation: 592

Use this. if you directly wanted to return column number of an empty range, it will show error. 

Sub findtest() 
dim c as long
dim rng as range
   Set rng = ActiveSheet.Rows("1:1").Find(What:="John Smith", _
        After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByColumns, _
        SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

    If Not rng Is Nothing Then c = rng.column : Debug.Print c
end sub

Upvotes: 2

Related Questions