Creating Totals row in table with VBA

[unknown_user1337]

Having trouble finding the answer to this anywhere.. I need to add a Totals row to a dynamically created table with VBA, using a custom formula for the totals. Each row in the table has an "x" or is blank for each column, and I need to get the count of "x"'s for each column. What I have for code, after inserting data into a column:

                dest.ListObjects(destTbl).ListColumns(col).TotalsCalculation = xlTotalsCalculationCustom
                dest.ListObjects(destTbl).ListColumns(col).Total.Formula = "=COUNTIF([" & col & "]," & Chr(34) & "x" & Chr(34) & ")"

This just returns an error for "Object variable or With block variable not set" on the last line above.. There is no documentation on how to use xlTotalsCalculationCustom, and I found the above solution buried somewhere in a stackoverflow answer.. So if this isn't right, what is??

 

[Fluff]

Hi & welcome to MrExcel.
What is col?

 

[unknown_user1337]

Hi & welcome to MrExcel.
What is col?

Hi, col is just a variable holding the name of the column. For example, "Login"

 

[Fluff]

Ok, in that case try

   With dest.ListObjects(destTbl)
      .ShowTotals = True
      .ListColumns(col).Total.FormulaR1C1 = "=COUNTIF([" & col & "],""X"")"
   End With

 

[unknown_user1337]

Ok, in that case try

   With dest.ListObjects(destTbl)
      .ShowTotals = True
      .ListColumns(col).Total.FormulaR1C1 = "=COUNTIF([" & col & "],""X"")"
   End With

Alright, I got it. Seems the trick was to put .ShowTotals = True before the formula. Thanks!

 

[Fluff]

You're welcome & thanks for the feedback.