CODEWITHSUNDEEP
excelvbafind
jan.f
jan.f

Reputation: 15

VBA Find value not found

I need some help as I am beginner in VBA. I have code that is looking for value "xstart" in multiple worksheets to define the range of the cells that needs to be deleted. I am having problem in the situation when there is no value "xstart" exsiting in the worksheet. How can I handle it?

Sub TestReset()

YesNo = MsgBox("Are you sure you want to clear the data?", vbYesNo)

Select Case YesNo
    Case vbYes
        Dim sht As Worksheet
        For Each sht In ActiveWorkbook.Sheets
            If sht.Name <> "Sheet1" And sht.Name <> "Sheet2" Then
                Dim iRow As Long, iMax As Long
                iRow = sht.Cells.Find(What:="xstart", LookIn:=xlFormulas, _
                       LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                       MatchCase:=False, SearchFormat:=False).Row

                iMax = sht.Cells(iRow, "A").End(xlDown).Row
                sht.Range("A" & iRow & ":AY" & iMax).ClearContents
            End If
        Next sht

    MsgBox ("Data has been cleared.")

Case vbNo

End Select

End Sub

Upvotes: 1

Views: 1646

Answers (2)

Tim Stack
Tim Stack

Reputation: 3248

Simply check if the variable that stores the Find object is empty:

Sub TestReset()
Dim iRow As Range
Dim iMax As Long
Dim sht As Worksheet

YesNo = MsgBox("Are you sure you want to clear the data?", vbYesNo)

If YesNo = vbYes Then
    For Each sht In ActiveWorkbook.Sheets
        If sht.Name <> "Sheet1" And sht.Name <> "Sheet2" Then
            Set iRow = sht.Cells.Find(What:="xstart", LookIn:=xlFormulas, _
                   LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                   MatchCase:=False, SearchFormat:=False)
            If Not iRow Is Nothing Then
                iMax = sht.Cells(iRow.Row, "A").End(xlDown).Row
                sht.Range("A" & iRow.Row & ":AY" & iMax).ClearContents
            End If
        End If
    Next sht

    MsgBox ("Data has been cleared.")

End If

End Sub

Upvotes: 1

BDra
BDra

Reputation: 506

If the phrase you are searching for is found, the Range.Find method returns a Range object, namely the cell where the phrase was found. If the phrase is not found, the method returns Nothing.

You can read more here: Range.Find method (Excel)

That's the problem you are running into. When "xstart" is found, sht.Cells.Find(...) returns a cell, and you can reference the Row property of that cell. When "xstart" is not found, the Find method returns Nothing. Nothing doesn't have a Row property, so sht.Cells.Find(...).Row fails.

To prevent a program error, you need to add a check for whether the Find method returned Nothing, like this:

Set MyRange = sht.Cells.Find(What:="xstart", LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)

If Not MyRange Is Nothing Then
    iRow = MyRange.Row
    'etc.

Observe that you do not check whether iRow (the desired row number) is Nothing. You check whether Find returns a range or Nothing. If it returns a range, you can then read the Row property of that range.

Upvotes: 1

Related Questions