Add every third column together, two variable cells in one VBA

Question

I have a spreadsheet which summarises discrepancies tellers takings and breaks them down into cash, card and cheque transactions. I have attempted to run create a VBA which will add together the total cash (etc) discrepancies for the year to date and I’ve become completely stuck. Is this something I can do with two variables?

It’s also important to note that when I reconcile the tellers takings to calculate the discrepancies my VBA it inserts 3 columns between D & E and adds the new discrepancies for that day.

Column A = Teller name (this number can varry over time)

Column B = Total Cash Discrepancies

Column C = Total Card Discrepancies

Column D = Total Cheque Discrepancies

[these are the columns which I would like to show the running totals]

Column E = 05/07/2014 Cash Discrepancy

Column F = 05/07/2014 Card Discrepancy

Column G = 05/07/2014 Cheque Discrepancy

Column H = 04/07/2014 Cash Discrepancy

ETC

I have tried:

For i = 2 To WSlr ‘last row
WS.Cells(i,2).Value = WorksheetFunction.Sum(Range("E" & i), Range("H" & i), Range("K" & i), Range("N" & i), Range("Q" & i), Range("T" & i)) 
Next i

. . . etc but as the sum function only allows me to input 30 numbers (rangers) this will only last me 10 days. Not to mention this was incredibly time consuming to enter.

• I’ve also tried pasting a formula in the cells:

ActiveCell.FormulaR1C1 =  "=SUM(RC5,RC8,RC11,RC14,RC17,RC20,RC23,RC26,RC29,RC32,RC35,RC38,RC41,RC44,RC47,RC50,RC53,RC56,RC59,RC62,RC65,RC68,RC71,RC74,RC77,RC80,RC83,RC86,RC89,RC92,RC95,RC98,RC101,RC104,RC107)"

But when I insert the next dates takings this no longer works (as it skips the new column entered).

Can anyone suggest a more effective way of doing this?

Thank you in advance.

lowak lowak · Accepted Answer · 2014-07-31T06:57:23

If you want to sum every third column or row you should use a loop. What type of loop to use depends on your exact problem.

In your first code you want to sum every third column, which could look like this:

Dim sum12 As Double 'type depends on data you have in your cells
Dim y As Integer, i As Integer

For i = 2 To WSlr ' last row

sum12 = 0

    For y = 5 To 100 Step 3

        sum12 = sum12 + Cells(y, i)

    Next y

WS.Cells(i, 2).Value = sum12

Next i

You need to adjust y, because I have no idea how many column you need to sum up.

This code will add every third column cell in a row. After adding all given cells in a row it will move to a next row until it reaches WSlr (your last row).

You can read more about loop for example here: http://www.ozgrid.com/VBA/loops.htm

Add every third column together, two variable cells in one VBA

1 Answers