How use vba SUM function in specific row for two columns

[Ali M]

Hello
I want to sum numeric numbers for columns D,E and show result in TOTAL row .
the data begins B2:F so should sum numeric values from D2,E2 until reach lastrow contains TOTAL word in column B .every time should update the summing in TOTAL row for columns D,E when run the macro every time.
how can I use vba WorksheetFunction.Sum for that ,guys?

 

[welshgasman]

I would find the totals row and deduct a bumber to suit your sheet.
Then set the Totals cell value to that formula.

However if the Totals row is not moving, you can hard code that formula.

 

[Ali M]

Then set the Totals cell value to that formula.

I can't because the TOTAL row will change in location every time , is not fixing . that's why I want vba

 

[welshgasman]

You can as you will know the row where the totals are.

 

[Joe4]

Please show us an example of your data layout and expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Sufiyan97]

Removed as Joe already posted.

 

[Ali M]

ok
example
before

q
	B	C	D	E	F
1	BATCH	SER.NO	INPUT	OUTPUT	NOTE
2	SDD-001	S-001	22	1
3	SDD-002	S-002	20
4	SDD-003	S-003	12	1
5	SDD-004	S-004	11
6	SDD-005	S-005	1	2
7	TOTAL
sh

after

q
	B	C	D	E	F
1	BATCH	SER.NO	INPUT	OUTPUT	NOTE
2	SDD-001	S-001	22	1
3	SDD-002	S-002	20
4	SDD-003	S-003	12	1
5	SDD-004	S-004	11
6	SDD-005	S-005	1	2
7	TOTAL		66	4
sh

Cell Formulas
Range	Formula
D7:E7	D7	=SUM(D2:D6)

another example

before

q
	B	C	D	E	F
1	BATCH	SER.NO	INPUT	OUTPUT	NOTE
2	SDD-001	S-001	22	1
3	SDD-002	S-002	20
4	SDD-003	S-003	12
5	SDD-004	S-004	11
6	TOTAL
sh

after

q
	B	C	D	E	F
1	BATCH	SER.NO	INPUT	OUTPUT	NOTE
2	SDD-001	S-001	22	1
3	SDD-002	S-002	20
4	SDD-003	S-003	12
5	SDD-004	S-004	11
6	TOTAL		65	1
sh

Cell Formulas
Range	Formula
D6:E6	D6	=SUM(D2:D5)

 

[Joe4]

See if this does what you want:

Sub AddTotals()

    Dim lr As Long
    
'   Find last row in column B with a value
    lr = Cells(Rows.Count, "B").End(xlUp).Row
    
'   If last row in column B is "TOTAL", add totals to columns D and E
    If Cells(lr, "B").Value = "TOTAL" Then
        Cells(lr, "D").Formula = "=SUM(D2:D" & lr - 1 & ")"
        Cells(lr, "E").Formula = "=SUM(E2:E" & lr - 1 & ")"
    End If
    
End Sub

 

[Ali M]

Awesome !
thanks very much , Joe.

 

[Joe4]

You are welcome.
Glad we were able to help!