Excel VBA error 91: Object variable or With block variable not set

[GeriJ]

I can't find my error. I've tried Adding "Set" in front of "colcnt = rng.Columns.Count", where the error is highlighted. I tried several other changes, but no luck. I'm using Excel 2011 on a Mac.

Please help!

Sub FindSales()
    Dim rng As Range
    Dim colcnt As Long
    Dim sortv As String
    Dim x As Long
    
    Set rng = Range("VecC1")
    sortv = "Sales"
    colcnt = rng.Columns.Count
    
        For x = 1 To colcnt
            If rng.Cells(1, x).Value = sortv Then
                rng.Cells(1, x).EntireColumn.Hidden = False
            Else
                rng.Cells(1, x).EntireColumn.Hidden = True
            End If
        Next x
            Range("A1").Select


End Sub

 

[RoryA]

Welcome to the forum.

Your code looks fine to me as posted. I certainly can't see how you'd get an error 91 from it. You would get a different error if the range name were wrong.

 

[GeriJ]

Nevertheless, this is what is happening. I have been trying different permutations all day. Still scratching my head.
Geri

 

[Anthony47]

A silly suggestion:
copy the macro form your message and paste it into your project (replacing the current code), and test it

When in debug mode (after the error), press Cont-g to open the vba "Immediate window" and type

?rng.address

What does it respond?

Also, can you share a small test workbook?

Bye

 

[GeriJ]

Thanks, Anthony,

I have no idea as to why, but here's what worked. In the original vba, I call the "FindSales()" macro in a variety of smaller macros, redefining sortv for each. When I copied and pasted the code, it worked the first time, but not the second!

Inspired to try odd things, I moved the "Dim sortv as String" from all the macros that called FindSales(), to the universal top. That worked!

The error message was not helpful and the highlighted code was a misdirect. I've got to study up of debugging!

Your suggestion, gave me a new way to approach the problem, so, thanks!

Geri

A silly suggestion:
copy the macro form your message and paste it into your project (replacing the current code), and test it

When in debug mode (after the error), press Cont-g to open the vba "Immediate window" and type

?rng.address

What does it respond?

Also, can you share a small test workbook?

Bye