Find a match in workbook using Excel VBA: run-time error 1004

Question

I am trying to create a macro which searches for the word "Routing" in all sheets of my active workbook.

When it detects the first occurrence in one of my sheets of my workbook, I want my macro to select the related cell on the corresponding sheet.

My macro returns the error message:

run-time error 1004: activate method of range class failed…

It seems this line of code generates the issue: Loc.Activate

Sub FindAndExecuteRouting()
Dim Sh As Worksheet
Dim Loc As Range
For Each Sh In ActiveWorkbook.Worksheets
    With Sh.UsedRange
        Set Loc = .Cells.Find(What:="Routing")
        If Not Loc Is Nothing Then
            Loc.Activate
        End If
    End With
Next
End Sub

Mathieu Guindon · Accepted Answer · 2019-12-08T17:58:08.100

You can't Activate a Range unless it's on the ActiveSheet.

So you'd have to do Sh.Activate before you can do Loc.Activate. Note that normally you do not want to Activate a cell you already have a reference to, but when the goal is to select a cell so that the user can see the selection box around a specific cell... well, that's one of the very few legit use cases for Worksheet.Activate =)

Public Sub FindAndExecuteRouting()
    Dim Sh As Worksheet
    Dim Loc As Range
    For Each Sh In ActiveWorkbook.Worksheets
        With Sh.UsedRange
            Set Loc = .Find(What:="Routing")
            If Not Loc Is Nothing Then
                Sh.Activate
                Loc.Activate
                Exit Sub
            End If
        End With
    Next
End Sub

Note the Exit Sub: you want to stop looping (Exit For would work as well) once you've found one, otherwise you'll just successively select every result very quickly, and only the last one will end up selected.

Consider using an indenter to help keep indentation consistent!

Find a match in workbook using Excel VBA: run-time error 1004

1 Answers1