Can you help me debug this code?

[saftawy1]

Dim lastRow As Long, i as long
lastRow = ThisWorkbook.Sheets("report").Range("b1000000").End(xlUp).Row
lastRow = lastRow - 1
i = lastRow
ThisWorkbook.Sheets("report").Range("c&i&i").Formula = "=SUBTOTAL(109,$c$2:i)"

What is meant by the code is to add a sum to specific columns starting from the last row containing data

test-total column.xlsb
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	p1	p2	p3	p4	p5	p6	p7	p8	p9	p10	p11	p12	p13	p14	p15
2	1	1	1	1	1	1	1	1	1	1	1	1	2	2	2
3	1	1	1	1	1	1	1	1	1	1	1	1	2	2	2
4	1	1	1	1	1	1	1	1	1	1	1	1	2	2	2
5	1	1	1	1	1	1	1	1	1	1	1	1	2	2	2
6	1	1	1	1	1	1	1	1	1	1	1	1	2	2	2
7	1	1	1	1	1	1	1	1	1	1	1	1	2	2	2
8	total
report

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B7:P7	Expression	=$AT7="هديه"	text	NO
B7:P7	Expression	=$AT7="مقعد مجانى"	text	NO
B7:P7	Expression	=$AT7="مجمد"	text	NO
B7:P7	Expression	=$AT7="comp"	text	NO
B7:P7	Expression	=$AT7="pc"	text	NO
B7:P7	Expression	=$AT7="cxl"	text	NO
B7:P7	Expression	=$AT7="cash"	text	NO
B6:P6	Expression	=$AT6="هديه"	text	NO
B6:P6	Expression	=$AT6="مقعد مجانى"	text	NO
B6:P6	Expression	=$AT6="مجمد"	text	NO
B6:P6	Expression	=$AT6="comp"	text	NO
B6:P6	Expression	=$AT6="pc"	text	NO
B6:P6	Expression	=$AT6="cxl"	text	NO
B6:P6	Expression	=$AT6="cash"	text	NO
B5:P5	Expression	=$AT5="هديه"	text	NO
B5:P5	Expression	=$AT5="مقعد مجانى"	text	NO
B5:P5	Expression	=$AT5="مجمد"	text	NO
B5:P5	Expression	=$AT5="comp"	text	NO
B5:P5	Expression	=$AT5="pc"	text	NO
B5:P5	Expression	=$AT5="cxl"	text	NO
B5:P5	Expression	=$AT5="cash"	text	NO
B4:P4	Expression	=$AT4="هديه"	text	NO
B4:P4	Expression	=$AT4="مقعد مجانى"	text	NO
B4:P4	Expression	=$AT4="مجمد"	text	NO
B4:P4	Expression	=$AT4="comp"	text	NO
B4:P4	Expression	=$AT4="pc"	text	NO
B4:P4	Expression	=$AT4="cxl"	text	NO
B4:P4	Expression	=$AT4="cash"	text	NO
B3:P3	Expression	=$AT3="هديه"	text	NO
B3:P3	Expression	=$AT3="مقعد مجانى"	text	NO
B3:P3	Expression	=$AT3="مجمد"	text	NO
B3:P3	Expression	=$AT3="comp"	text	NO
B3:P3	Expression	=$AT3="pc"	text	NO
B3:P3	Expression	=$AT3="cxl"	text	NO
B3:P3	Expression	=$AT3="cash"	text	NO
B2:P2	Expression	=$AT2="هديه"	text	NO
B2:P2	Expression	=$AT2="مقعد مجانى"	text	NO
B2:P2	Expression	=$AT2="مجمد"	text	NO
B2:P2	Expression	=$AT2="comp"	text	NO
B2:P2	Expression	=$AT2="pc"	text	NO
B2:P2	Expression	=$AT2="cxl"	text	NO
B2:P2	Expression	=$AT2="cash"	text	NO

 

[Alex Blakenburg]

See if this is what you had in mind:

Sub SubTotalColumn()

    Dim lastRow As Long, lastRowData As Long
    'lastRow = ThisWorkbook.Sheets("report").Range("b1000000").End(xlUp).Row
    lastRow = ThisWorkbook.Sheets("report").Range("b" & Rows.Count).End(xlUp).Row
    lastRowData = lastRow - 1
    
    With ThisWorkbook.Sheets("report")
        .Range(.Cells(lastRow, "C"), .Cells(lastRow, "P")).Formula = "=SUBTOTAL(109,C$2:C$" & lastRowData & ")"
    End With

End Sub

 

[saftawy1]

See if this is what you had in mind:

Sub SubTotalColumn()

    Dim lastRow As Long, lastRowData As Long
    'lastRow = ThisWorkbook.Sheets("report").Range("b1000000").End(xlUp).Row
    lastRow = ThisWorkbook.Sheets("report").Range("b" & Rows.Count).End(xlUp).Row
    lastRowData = lastRow - 1
   
    With ThisWorkbook.Sheets("report")
        .Range(.Cells(lastRow, "C"), .Cells(lastRow, "P")).Formula = "=SUBTOTAL(109,C$2:C$" & lastRowData & ")"
    End With

End Sub

Thank you very much

Alex Blakenburg​

its excelent code

 

[Alex Blakenburg]

No problem. Glad I could help.