variable not set error?

[davegb]

I got some help on writing a program to hide certain columns on every
page in a workbook. Works great. But I modified the code I had written
to do the same, just didn't put it the If statement, and it won't run.
Here are the 2 programs:

Sub AllSheetsColHide()
'Doesn't work
'for all sheets in currently active workbook, assigned to button


Dim TopCell As Range
Dim TopCol As Range
Dim Cols2Hide As Range
Dim wkSht As Worksheet

For Each wkSht In ActiveWorkbook.Worksheets
With wkSht
Set TopCell = .Rows(3).Find(What:="top", LookIn:=xlValues)
Set TopCol = .Columns(TopCell.Column)<--[error 911 - object
variable or with block variable not set]
Set Cols2Hide = .Range(TopCol, .Columns("AC"))
Cols2Hide.Hidden = True

End With

Next wkSht

End Sub

Sub SheetsColHide()
'for all sheets in currently active workbook, assigned to button
Dim TopCell As Range
Dim TopCol As Range
Dim Cols2Hide As Range
Dim wkSht As Worksheet

For Each wkSht In ActiveWorkbook.Worksheets
With wkSht
Set TopCell = .Rows(3).Find(What:="top", LookIn:=xlValues)
If Not TopCell Is Nothing Then ' if it found "top"
Set TopCol = .Columns(TopCell.Column)
Set Cols2Hide = .Range(TopCol, .Columns("AC"))
Cols2Hide.Hidden = True
End If
End With
Next wkSht
End Sub

Can anyone tell me why I get the error in the first program, but not in
the second?
Thanks in advance!

 

[Chip Pearson]

You'll get that error if the Find method fails to find a match.
When Find fails to find a match, it sets the return variable to
Nothing. In your second procedure, you are testing this
condition, and only using the TopCell variable if it is not
Nothing.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[davegb]

Thanks for your reply, Chip. Duh! Should have figured that one out! But
I forgot the the column with the word Top in it was hidden in the first
sheet in the workbook. And the message confused me, didn't think I'd
get that message from the find not finding anything.

 

[Rowan]

Dave

The second macro checks that the word "top" has been found on the sheet
before trying to use the variable TopCell. If "top" is not found then TopCell
is not set i.e it is Nothing and so you can't use it to set the variable
TopCol.

Hope this makes sense
Rowan

 

[davegb]

Ok, that makes more sense. Thanks for taking the time to explain
further!