How to set a range using the find function

Question

I have a stylized spreadsheet with formatted cells for users to input data. I am trying to create a macro for use with a button to clear all of the input cells at once. However, I am struggling with the "find" and "findformat" functions.

To make it simple, in this code I am just looking for cells that say "Retail". When I run the code, the value of myRange is always Nothing even though there is clearly a cell in the spreadsheet that has the value "Retail". Any ideas why the range is Nothing?

    Public Sub reset()
    'reset all input fields to no value
    msg = MsgBox("Are you sure you want to delete all data and reset all files to original state?", vbYesNoCancel, "***Warning***")
    If msg = vbYes Then

Dim inputCell As Long
Dim noteCell As Long
inputCell = RGB(255, 204, 153)
noteCell = RGB(255, 255, 204)

Dim myRange As Range
Dim mySheet As Worksheet

Dim shp As Shape
Dim sht As Worksheet
Dim objXL As Object
Dim wb As Workbook
Dim pathName, name, myLink As String

Set sht = ActiveSheet
Set wb = ActiveWorkbook

pathName = wb.FullName
name = wb.name

For Each shp In sht.Shapes
    If shp.Type = msoGroup Then
        For i = 1 To shp.GroupItems.Count
            If shp.GroupItems(i).Type = msoEmbeddedOLEObject Then
                shp.GroupItems(i).Select
                shp.GroupItems(i).OLEFormat.Activate
                Set wb = ActiveWorkbook
                If Not IsEmpty(wb.LinkSources(xlExcelLinks)) Then
                    For Each link In wb.LinkSources(xlExcelLinks)
                        On Error Resume Next
                        wb.ChangeLink name:=link, newName:=pathName, Type:=xlLinkTypeExcelLinks
                    Next link
                End If

                For Each mySheet In ActiveWorkbook.Worksheets

                    With Application.FindFormat.Interior.Color = inputCell
                        myRange = mySheet.Cells.Find(what:="Retail")   ', searchformat:=True)
                        myRange.ClearContents
                    End With

                Next mySheet

                wb.Close (False)
            End If
        Next i
    End If
Next shp
End If
End Sub

Answer 1

I changed my code as follows and now it works exactly as I wanted:

                For Each mySheet In ActiveWorkbook.Worksheets

                    With Application.FindFormat
                        .Interior.Color = inputCell

                        Do
                            On Error GoTo handler:
                            Set myRange = mySheet.Cells.Find(what:="?*", searchformat:=True).MergeArea
                            If Not (myRange Is Nothing) Then
                                myRange.ClearContents
                            End If
                        Loop While Not (myRange Is Nothing)

                        .Interior.Color = noteCell

                        Do
                            On Error GoTo handler:
                            Set myRange = mySheet.Cells.Find(what:="?*", searchformat:=True).MergeArea
                            If Not (myRange Is Nothing) Then
                                myRange.ClearContents
                            End If
                        Loop While Not (myRange Is Nothing)

handler:
Set myRange = Nothing
Resume Next

                    End With

                Next mySheet

I'm just not sure if this kind of error handling is the best way to deal with the problem, and I don't understand why an error occurs in the first place. So if anyone has a thought on this, I would appreciate it. If not, I'm just happy it works now.

Answer 2

I refer to the FindFormat documentation for some example:

https://msdn.microsoft.com/en-us/library/office/ff838023.aspx

And modify your code thusly:

With Application.FindFormat
    .Interior.Color = inputCell
    Do
        Set myRange = mySheet.Cells.Find(what:="Retail", SearchFormat:=True)
        If myRange Is Nothing Then myRange.ClearContents
    Loop While Not myRange Is Nothing
End With

NOTE: You should be using the Set keyword when assigning to a range object myRange. Also, your improper use of On Error Resume Next may be masking additional errors which are adversely impacting the results of this function. You can rectify the latter issue like so:

            If Not IsEmpty(wb.LinkSources(xlExcelLinks)) Then
                For Each link In wb.LinkSources(xlExcelLinks)
                    On Error Resume Next
                    wb.ChangeLink name:=link, newName:=pathName, Type:=xlLinkTypeExcelLinks
                    On Error GoTo 0  '### RESUME NORMAL ERROR HANDLING
                Next link
            End If