Run-time Error'91: Object variable or With block variable not set

[Guest]

I am developing a macro that among other things hides columns where the text
"Index" is found. However I am encountering the following error:

Run-time Error'91: Object variable or With block variable not set

I have played around with it but I can't seem to get rid of that error..
Can anyone help?

Private Sub ColumnFinder()
Dim FirstAddress As String
Dim c As Variant
'Macro identifies Index columns
With ActiveSheet.Range("a6:iv6")
Set c = .Find("Index", LookIn:=xlValues)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
PresentAddress = c.Address
'c.Offset(-1, 0).Value = PresentAddress 'Check step
c.Value = "Index"
'c.Interior.ColorIndex = 3'check step
Call CycleThrough
' Discard columns
c.EntireColumn.Hidden = True<---------This is the problem
statement

Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With
End Sub

 

[Rowan]

You didn't post the code for CycleThrough so I don't know what that is
doing. Also are you sure the hide statement is giving the error. It
seems to me that this would be more likely to be the cause of your problems:
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress

If a column is not found containing Index then c will be nothing meaning
that c.Address will fail.

Hope this helps
Rowan

 

[Tushar Mehta]

What does CycleThrough do? Does it delete the column that includes the
cell that c refers to?

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Guest]

Thank you for responding. The CycleThrough macro simply performs conditional
formatting based on the content of the column where the 'Index' is found. i
think you may be on to something with the mention to the :

Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress

Even if I hide the column, I still should be able to reference it in vb.
Right???

I have checked and the 'c' value is empty. I altered the code slightly
adding a new replicate variable cStore but I am getting yet another error:

Run-time error '424': Object required

Now my code is:
Private Sub ColumnFinder()
Dim FirstAddress As String
Dim c As Variant
Dim cStore As Variant 'Range 'Variant
'Macro identifies Index columns
With ActiveSheet.Range("a6:iv6") 'Worksheets("Sheet3").Range("A6:IV6")
Set c = .Find("Index", LookIn:=xlValues)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
PresentAddress = c.Address
cStore = c.EntireColumn
'c.Offset(-1, 0).Value = PresentAddress 'Check step
c.Value = "Index"
'c.Interior.ColorIndex = 3'check step
Call CycleThrough
'play with Discard columns
'Columns("E:E").EntireColumn.Hidden = True
'Columns("AA:AA").Delete
'c.EntireColumn.Hidden = True
cStore.Hidden = True <--------This is now where it's
choking up
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With
End Sub

 

[Rowan]

How about a different take on this:

Private Sub ColumnFinder()
Dim c As Range
For Each c In ActiveSheet.Range("A6:IV6")
If c.Value Like "*Index*" Then
c.Value = "Index" 'are you sure you want to do this
Call CycleThrough
c.EntireColumn.Hidden = True
End If
Next c
End Sub

Hope this helps
Rowan