Runtime Error '91' Object variable or With block variable not set

[Alec Coliver]

I have a problem as per the subject line.
The problem region of code is listed below.
The actual error is occurring at this point of execution in the Sub
Next_Bold_Font_Found()


Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=True).Activate

The routine works perfectly in that all subtotals are identified and
formatted.
I tried different lists (they're dynamic) to make sure and no problems in
the formatting except for the macro going into a continuous loop.

Sub Format_All_Subtotals()
'
' Finds & Formats The First Transport Company Totals in Bold Case

Application.FindFormat.Font.Bold = True
Range("A50").Select
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=True).Activate
ActiveCell.Offset(0, 7).Range("A1:J1").Select
Selection.Font.Bold = True
ActiveCell.Offset(0, -7).Range("A1").Select
Application.Run "Test_Next_Cell_Down"

End Sub

Sub Next_Bold_Font_Found()
'
'Finds & Formats The Remaining Transport Company Totals in Bold Case

Application.FindFormat.Font.Bold = True
ActiveCell.Offset(1, 0).Select
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=True).Activate
ActiveCell.Offset(0, 7).Range("A1:K1").Select
Selection.Font.Bold = True
ActiveCell.Offset(0, -7).Range("A1").Select
ActiveCell.Offset(1, 0).Select

End Sub

Sub Test_Next_Cell_Down()
'
' Tests that the cell below Is Not Empty

Do
Application.Run "Next_Bold_Font_Found"
Loop While Not IsEmpty(ActiveCell)
Application.Run "Go_Home"
formWhatNext.Show

End Sub

Sorry if the code is as bit long winded, but I'm teaching myself VBA and
learning as I go.
Any help with this post is very much appreciated.

 

[Mike H]

Hi,

I can see where the code is going wrong but dont know what you're trying to
do. We start the search in A50. Let's say we find a bold cell in A55, what is
this bit of code supposed to do?

I can see you want to make something bold 7 columns to the right but the A1
-J1 has me stumped.

ActiveCell.Offset(0, 7).Range("A1:J1").Select
Selection.Font.Bold = True
ActiveCell.Offset(0, -7).Range("A1").Select

Mike

 

[Dave Peterson]

I think it's a good idea to set a variable to the results of the .find()
statement. Then you can check that to see if it was found.

Dim FoundCell as range
set foundcell = cells.find(What:="", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:= xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=True)

if foundcell is nothing then
'not found, don't do anything
else
foundcell.offset(0,7).Range("A1:J1").Font.Bold = True
end if


===========
And if you want to loop through your data, look at the example for FindNext in
VBA's help.

It'll find the first, keep track of where that was found, then keep finding
until the foundcell loops back to that first address.