Yet another, but probably simple, problem with ranges

I'm trying to set two dynamic ranges so that I can clear their contents. I've tried all sorts of different methods in an effort to selecting them, and I've isolated the problem. Yet I'm still unsure why the vba editor for excel is evaluating my code as it is. CatSubTot and Brkdn are the two sheets I am using, and I realize that there are probably redundancies in the code. The error that comes up is in the line:

range1 = Range("A9")

This seems like a very simple line....yet when I debug it, the range becomes "cat1", which is the value of cell A9. Shouldn't Range("A9").value = "cat1" ? I've also tried using cell references such as Range(R9C1), but still no luck. The error message that appears is "Error # 91: Object Variable or With Block Variable Not Set"
Any help would be greatly appreciated. Thanks in advance.


Sub Brkdn_Prices

Dim range1 As Object, range2 as Object

Sheets("CatSubTot").Activate
range1 = Range("A9")
Range(range1, range1.End(xlDown).End(xlToRight)).ClearContents

End Sub

Hi MarshallJ and welcome to the forum! :)

Please be sure to read the posting guidelines (http://www.xtremevbtalk.com/faq.php?faq=evbf_faq#faq_evbf_rules).

I'd give a go at declaring your variables as ranges. Why? Well, check out the "Don't Use Object Type Variables" section HERE (http://www.cpearson.com/excel/variables.htm).

Dim Range1 As Range, Range2 as Range



You need to use the set statement when assigning an object reference to a variable:

Set Range1 = Sheets("CatSubTot").Range("A9")


Check out the set statement in your helpfile and ask us any questions to make sure you're comfortable with it.
Put it all together and your sub looks like this:

Sub Brkdn_Prices
Dim Range1 As Range, Range2 as Range
Set Range1 = Sheets("CatSubTot").Range("A9")

'.....
End Sub

After that, you might find you get some more errors with the remaining portion of your code. Post back if you get stuck.

HTH
Colin

Thanks Colin, that was a huge help.