View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
davegb davegb is offline
external usenet poster
 
Posts: 573
Default variable not set error?

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!