Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
variable not set error?
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" wrote in message oups.com... 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
variable not set error?
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
variable not set error?
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" wrote: 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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
variable not set error?
Ok, that makes more sense. Thanks for taking the time to explain
further! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Runtime Error '91' Object variable or With block variable not set | Excel Discussion (Misc queries) | |||
Run-time error '91': "Object variable or With block variable not set | Excel Programming | |||
Error 91 -- Object variable or With block variable not set | Excel Programming | |||
Cells.Find error Object variable or With block variable not set | Excel Programming | |||
Error 91 - Object variable with block variable not set | Excel Programming |