VBA finding SUM of all cells to left of cell

[jardenp]

I need a VBA formula to enter in a cell in a certain row (row # = WeeklyTotalsAddRow variable) in the column with the header "Total" in row 1 that will give me the sum of all the cells to the left of this cell except columns A-C.

The column with the "Total" header could be anywhere from column G through BA.

I write to the cell with this:

Cells(WeeklyTotalsAddRow, Application.WorksheetFunction.Match("Total", Range("A1:BA1"), 0)).Formula = [U][B]???[/B][/U]

In this example, WeeklyTotalsAddRow is equal to 5, so the code would write "=SUM(D5:F5)" in G5 and result in "6" (previous rows already have sum formulas in Total column):
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Set[/TD]
[TD]Co.[/TD]
[TD]Sale1[/TD]
[TD]Sale2[/TD]
[TD]Sale3[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]X4[/TD]
[TD]456[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]=SUM(D1:F1)[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD]X5[/TD]
[TD]789[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]=SUM(D2:F2)[/TD]
[/TR]
[TR]
[TD]Jill[/TD]
[TD]X6[/TD]
[TD]101[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]=SUM(D3:F3)[/TD]
[/TR]
[TR]
[TD]Jess[/TD]
[TD]X7[/TD]
[TD]112[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Here it would write "=SUM(D5:G5)" in H5 and result in "8":
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Set[/TD]
[TD]Co.[/TD]
[TD]Sale1[/TD]
[TD]Sale2[/TD]
[TD]Sale3[/TD]
[TD]Sale4[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]X4[/TD]
[TD]456[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]=SUM(D1:G1)[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD]X5[/TD]
[TD]789[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]=SUM(D2:G2)[/TD]
[/TR]
[TR]
[TD]Jill[/TD]
[TD]X6[/TD]
[TD]101[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]=SUM(D3:G3)[/TD]
[/TR]
[TR]
[TD]Jess[/TD]
[TD]X7[/TD]
[TD]112[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I can figure out how to select the cell above it and autofill down, but the formula in Total may or may not be a straight SUM like I want, so that's not reliable.

Thanks!

Josh in IN

 

[Momentman]

Does it have to be with VBA? 'cos i worked out a formula that works

Excel 2010 32 bit
	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	Set	Co.	Sale1	Sale2	Sale3	Sale4	Sale5	Sale6	Sale7	Sale8	Total
2	Joe	X4	456	1	1	8	1	2	3	4	5	25
3	Jim	X5	789	1	4	1	2	5	6	7	8	34
4	Jill	X6	101	3	5	3	3	4	5	6	7	36
5	Jess	X7	112	3	7	2	0	0	0	3	2	17
Sheet8

Cell Formulas
Range	Formula
L2	=SUM(D2:INDIRECT(CHAR(65+MATCH("Total",$1:$1,0)-2)&ROW()))

 

[jardenp]

That seems to work fine if the column is AA or greater but it returns 0 when in columns E-Z. Thanks though! I appreciate your effort.

 

[Momentman]

That seems to work fine if the column is AA or greater but it returns 0 when in columns E-Z. Thanks though! I appreciate your effort.

Except you meant the reverse, it would work for columns D to Z, the example I posted above works in column L and you can test in other "SINGLE LETTERED" columns

Do you strictly need VBA or does a regular excel formula work for you?

 

[jardenp]

When I tested it on my actual data I found it worked with double-lettered columns, not single-lettered columns. I can't explain it because I don't fully grasp what you did.

A formula works fine--I think I can just use the macro recorder to "translate" it into VBA.

Thanks again for your help!

 

[Momentman]

When I tested it on my actual data I found it worked with double-lettered columns, not single-lettered columns. I can't explain it because I don't fully grasp what you did.

A formula works fine--I think I can just use the macro recorder to "translate" it into VBA.

Thanks again for your help!

The formula is getting longer by the minute please test to see that this works for all cases

Excel 2010 32 bit
	A	B	C	D	E	F	G	H	I	J
1	Name	Set	Co.	Sale1	Sale2	Sale3	Sale4	Sale7	Sale8	Total
2	Joe	X4	456	1	1	8	1	4	8	23
3	Jim	X5	789	1	4	1	2	7	5	20
4	Jill	X6	101	3	5	3	3	6	7	27
5	Jess	X7	112	3	7	2	0	3	2	17
Sheet8

Cell Formulas
Range	Formula
J2	=SUM(D2:INDIRECT(LEFT(SUBSTITUTE(ADDRESS(1,MATCH("Total",$1:$1,0)-1),"$",""), LEN(SUBSTITUTE(ADDRESS(1,MATCH("Total",$1:$1,0)-1),"$",""))-1) & ROW()))

 

[Momentman]

An easier variant to adopt in VBA

You only need to change the "ReqRow" to the row where you want the formula inserted, i have assumed row 2 for this example

Sub DisplayFormula()
    Dim coladrr As String
    Dim Coladdr2 As String
    Dim ReqRow As Long
    
    'Row to insert the formula
    ReqRow = 2
    
    'Column that has the word "Total"
    coladdr = Split(Range("1:1").Find("Total", Range("A1")).Address, "$")(1)
    
    'Column immediately to the left of the "Total" column
    Coladdr2 = Split(Range("1:1").Find("Total", Range("A1")).Offset(0, -1).Address, "$")(1)
    
    'Inserts the formula into the required row of the required column
    Cells(ReqRow, coladdr).Formula = "=SUM(D" & ReqRow & ":" & Coladdr2 & ReqRow & ")"
End Sub

 

[jardenp]

Works! Thank you so much! I really appreciate it.

 

[Rhodie72]

Try this: -
No formulae to slow down the job either.

Option Explicit
'   To start numbering from 1
Option Base 1
Sub VBA_Total()
'   Dimension variables
Dim myTitle As String, RngLen As Integer, _
    myArr() As Long, i As Long, _
    Ans As Long, Num_Cols As Long, Num_Rows As Long
    Dim myTotal, j
    Application.ScreenUpdating=False
' Find_Total
    Cells(1, 1).Select
    Cells.Find(What:="Total", After:=ActiveCell, LookIn:=xlValues, _
          LookAt:=xlPart, SearchOrder:=xlByColumns, _
          SearchDirection:=xlNext).Activate

' Select length of range to use, Discount the 1st 3 columns and the total column

    RngLen = Range(ActiveCell.Address & ":A1").Count - 4

' Count the number of Columns to use:
    ActiveCell.Offset(1, -RngLen).Activate
    Num_Cols = Range(Selection, Selection.End(xlToRight)).Count

' Count the number of rows to use:
    Num_Rows = Range(Selection, Selection.End(xlDown)).Count

' Instead of inserting a  formula for each row, we are going to calculate the answers _
    in RAM and place the values as a range selection which is at least 240x faster. _
    The first row to the last row is loaded into the array. _
    Set up a three dimensional array based upon the active selection, _
    Two dimensions for the existing data and the third dimension's _
    for the totals

    Ans = Num_Rows
    ReDim myArr(1 To Num_Rows, 1 To Ans)

'   a double nested dual-loop will transfer data to the array
    For j = 1 To Num_Rows
        myTotal = 0
        For i = 1 To Num_Cols
            myArr(j, i) = ActiveCell.Offset(0, i - 1).Value
            myTotal = myArr(j, i) + myTotal
        Next i
        myArr(j, 1) = myTotal
        With ActiveCell
            .Offset(, Num_Cols).Value = myArr(j, 1)
            .Offset(1, 0).Select
        End With
    Next j
    Application.ScreenUpdating=True
End Sub

 

[Peter_SSs]

It sounds like you already have the variable WeeklyTotalsAddRow so just add the following code

Dim rTotal As Range

Set rTotal = Rows(1).Find(What:="Total", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
If Not rTotal Is Nothing Then Cells(WeeklyTotalsAddRow, rTotal.Column).FormulaR1C1 = "=SUM(RC4:RC[-1])"