none
How do to ranges in an Excel macro dynamic? RRS feed

  • 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)
    Thursday, July 21, 2011 6:58 PM

Answers

  • When I execute the macro, I get the following:

    Run-time 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
    Thursday, July 21, 2011 8:03 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
    Thursday, July 21, 2011 7:29 PM
  • When I execute the macro, I get the following:

    Run-time 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
    Thursday, July 21, 2011 8:03 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
    • Marked as answer by TxDaisy Thursday, July 21, 2011 10:53 PM
    • Unmarked as answer by TxDaisy Thursday, July 21, 2011 10:53 PM
    Thursday, July 21, 2011 10:32 PM
  • Sorry, I'm relatively new to VB and learning a lot on the fly.  I realized that I did not include the first 2 lines of the code you gave me.  Once I added the additional 2 lines it worked like a charm.  THANK YOU SO MUCH!!!!

    Thursday, July 21, 2011 10:53 PM