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!
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)"
Range("RM_2015").Offset(, -1).Formula = "=SUM(AI2:AK2)"
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)"
Teens_2015is not a valid method of theWorksheetsobject. You can't chain variables like that. Remove the Worksheets from in front of it so that it's justTeens_2015.Formula = "=SUM(AI2:AK2)"