Formula Subtracting a Fixed Table Total Row from a Variable Table Total Row

  • I have a macro that adds a 2 new tables to a sheet, a new table for each day to the right of the previous day tables (the 2 daily tables are vertically stacked). There are total rows in the new tables, and I need to insert a formula that calculates the difference between one of the total columns for the new day and the previous day's total. I recorded a macro that I planned to edit to include variable references rather than fixed references. I'm now stuck at how to address the second part of the formula below:

    Code
    1. Cases051920[[#Totals],[Death as % of Total Cases]]

    The "Cases051920" table name is the variable. Each day, it will be a different date. Today's is "Cases052020", and tomorrow's new table will need to reference this table - and so on. How do I turn this into a variable so the macro can insert the formula so that it references the correct prior day's table???


    This is the recorded bit of code that I'm trying to adapt:

    Code
    1. Range("NewChange[[#Totals],[Death as % of Total Cases]]").Select
    2. Application.CutCopyMode = False
    3. ActiveCell.FormulaR1C1 = _
    4. "=NewTable[[#Totals],[Death as % of Total Cases]]-Cases051920[[#Totals],[Death as % of Total Cases]]"
  • Update: I've partially solved my problem but am stumped at the error I get now.

    I now get the RunTime Error 1004 application defined or object defined error when I get to this line:

    Code
    1. Range("NewChange[[#Totals],[Death as % of Total Cases]]").FormulaR1C1 = DeathCalc

    I am completely baffled, because it is the exact same syntax as I used in the two lines above it and those work 100% perfect. What am I doing wrong??


  • Solved it. Here's the solution if anyone ever comes across this:

    Had to rewrite the formula to use the variable pTable reference by adding "& pTable&" to pull the variable name into the formula

    Code
    1. DeathCalc = "=NewTable[[#Totals],[Death as % of Total Cases]] -" & pTable & "[[#Totals],[Death as % of Total Cases]]"