CODEWITHSUNDEEP
vbaexcel
Will Clein
Will Clein

Reputation: 11

Set formula for defined range in Excel VBA

I'm trying to apply a formula to an entire defined range as follows:

    Set Teens_2015 = Range("RM_2015").Offset(, -1)
        Worksheets(5).Teens_2015.Formula = "=SUM(AI2:AK2)"

Where RM_2015 is a named range in my workbook. I get the following error when I run this:

    Runtime error'438':
    Object doesn't support this property or method

Can someone clue me in as to what I'm doing wrong here?

Thanks!

Upvotes: 0

Views: 521

Answers (3)

Charles Williams
Charles Williams

Reputation: 23505

It would be better to fully qualify the range object and then use that, otherwise you create a conflict by trying to use a range from the active sheet as belonging to another sheet: presumably you want Teens_2015 to refer to sheet 5 rather than the active sheet.

Set Teens_2015 = Worksheets(5).Range("RM_2015").Offset(, -1)
Teens_2015.Formula = "=SUM(AI2:AK2)"

Upvotes: 1

KBerstene
KBerstene

Reputation: 73

It's "Worksheets(5)" that doesn't support the "Teens_2015" method. If you remove "Worksheets(5)" and just refer directly to the variable you set, it works fine:

Set Teens_2015 = Range("RM_2015").Offset(, -1)
Teens_2015.Formula = "=SUM(AI2:AK2)"

Upvotes: 0

Niclas
Niclas

Reputation: 1252

Range("RM_2015").Offset(, -1).Formula = "=SUM(AI2:AK2)"

Upvotes: 0

Related Questions