Cells.Find not working with multiple workbooks

[DGFA]

I'm kind of a novice with VBA, and I'm having an issue with performing a "find" function on multiple workbooks. The goal of the macro is to look in each open workbook for the text "Trains and Planes" which will always be in column A and then clear the contents of that row and all rows below it. I salvaged most of this code from something I found on the internet. When I run it, it says "Run time error 91, Object variable or with block variable not set" and it highlights the lines Cells.Find through False).Activate in the debugger. I understand that this means it can't find the text, but I'm not sure why. If it makes any difference, this is in Personal.xlsb. Here's the code I have now:

Sub Test1()
Dim wb As Workbook
Dim LastRow As Long, myCell As Range, myRange As Range
Dim myCell1 As Range

For Each wb In Workbooks
wb.Activate

LastRow = ActiveCell.SpecialCells(xlCellTypeLastCell).Row
Set myCell1 = Range("A" & LastRow)

Cells.Find(What:="Trains and Planes", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
Set myCell = ActiveCell
 
Set myRange = Range(myCell, myCell1)
myRange.EntireRow.ClearContents

Application.CutCopyMode = False

Next wb
End Sub

Any help would be greatly appreciated!

[xladept]

Hi DGFA,

Welcome to the Forum!

You need to test each sheet in each book.

[DGFA]

Hi, xladept!

Individually, each sheet works fine.

[HaHoBe]

Hi, DGFA,

maybe try this code (untested):

Sub Test1()
Dim wb As Workbook
Dim ws As Worksheet
Dim LastRow As Long, myCell As Range
Dim myCell1 As Range

For Each wb In Workbooks
  For Each ws In wb.Worksheets
    LastRow = ws.Cells.SpecialCells(xlCellTypeLastCell).Row
    Set myCell1 = ws.Range("A" & LastRow)
    
    Set myCell = ws.Columns("A:A").Find(What:="Trains and Planes", After:=myCell1, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    If Not myCell Is Nothing Then
      ws.Range(myCell, myCell1).EntireRow.ClearContents
      Application.CutCopyMode = False
      Set myCell = Nothing
    End If
  Next ws
Next wb
End Sub

Ciao,
Holger

[DGFA]

Hi, Holger!

I ran it, but it only modified the first workbook and never touched the second one. I think it actually might've repeated itself again on the first sheet instead.

[HaHoBe]

Hi, DGFA,

just to make clear: the workbooks need to be opened in the same instance of Excel to make the code work.

Ciao,
Holger

[DGFA]

Hi Holger,

They were both open at the same time.

Thanks,

DGFA