Answered by:
How do to ranges in an Excel macro dynamic?
Question

I have created a Pivot Table in Excel. It is a relatively simple Pivot Table, with only 2 columns – Column A (Item Description) and Column B (Units). The page of the Pivot Table is Category. The Page (or Category) will change from one time to the next, so the number of rows in the spreadsheet will vary.
I need to add to additional columns – Column C (% of Total) and Column D (Cumulative % of Total).
The % of Total (Column C) is a simple Excel formula in Cell C12 “=SUM(B13:B__)”. The Cumulative % of Total (Column D) is a simple Excel formula in D13 “+B13/$C$12”.
My initial Macro works fine. I have 2 problems I can’t seem to figure out how to fix. 1) the range in my formula in Cell C12 needs to be dynamic, and 2) when I autofill Columns C & D from Row 13 it stops at the last row of data which also needs to be dynamic. Here’s the macro:
Range("C11").Select
ActiveCell.FormulaR1C1 = "% of Total"
Range("D11").Select
ActiveCell.FormulaR1C1 = "Cum % of Total"
Range("C12").Select
ActiveCell.FormulaR1C1 = "=SUM(R[1]C[1]:R[198]C[1])"
Range("C13").Select
ActiveCell.FormulaR1C1 = "=+RC[1]/R12C3"
Range("D13").Select
ActiveCell.FormulaR1C1 = "=+RC[1]+R[1]C"
Range("C13:D13").Select
Selection.AutoFill Destination:=Range("C13:D210")
Range("C13:D210").Select
I’ve now spent a couple of days searching for a solution on the web, but just don’t have enough knowledge of VB to figure it out and make it work. Any help is greatly appreciated.
 Moved by kleinma Thursday, July 21, 2011 7:05 PM (From:Visual Basic General)
Answers

When I execute the macro, I get the following:
Runtime error '1004':
Method'Range'of object'_Global'failed
When I debug the following line of text is highlighted:
Range("C13:C" & myR).FormulaR1C1 = "=+RC[1]/R12C3"
Could this have something to do with the fact that I'm using Excel 2003, VB 6.5? I meant to include this in my original post, because I know in some cases this will make a difference.
 Marked as answer by TxDaisy Thursday, July 21, 2011 10:54 PM
All replies

Dim myR As Long
myR = Cells(Rows.Count, 2).End(xlUp).Row
Range("C11").Value = "% of Total"
Range("D11").Value = "Cum % of Total"
Range("C12").FormulaR1C1 = "=SUM(R[1]C[1]:R[" & myR  12 & "]C[1])"
Range("C13:C" & myR).FormulaR1C1 = "=+RC[1]/R12C3"
Range("D13:D" & myR).FormulaR1C1 = "=+RC[1]+R[1]C"
HTH, Bernie Proposed as answer by Bernie Deitrick, Excel MVP 20002010 Friday, July 22, 2011 1:11 AM
 Unproposed as answer by Bernie Deitrick, Excel MVP 20002010 Friday, July 22, 2011 1:11 AM

When I execute the macro, I get the following:
Runtime error '1004':
Method'Range'of object'_Global'failed
When I debug the following line of text is highlighted:
Range("C13:C" & myR).FormulaR1C1 = "=+RC[1]/R12C3"
Could this have something to do with the fact that I'm using Excel 2003, VB 6.5? I meant to include this in my original post, because I know in some cases this will make a difference.
 Marked as answer by TxDaisy Thursday, July 21, 2011 10:54 PM

If you are doing it from within Excel, it should work, especially since the other lines appeared to have worked. Hover over myR  what value does it have?
HTH, Bernie 