Trying to use VBA to insert a formula into a range of cells within a row

R

Rafaa

New Member
Joined
Aug 29, 2013
Messages
23
Hi,

I'm trying to use the following code to insert that formula into a range of cells (F:Q) of the rows where "Total" appears on column D. I'm only verifying if "Total" is on column D from row 8 to the last row containing data, which I get using the function LastRowIndex. I'm running excel 2011 for MAC.

I can't find the problem. It might be the way I am specifying the range. Any help will be much appreciated!

Thanks!


The LastRowIndex function and this formula were tested and are working properly:
=SUM(INDIRECT(CHAR(COLUMN()+64)&ROW()-INDIRECT("C"&ROW())):INDIRECT(CHAR(COLUMN()+64)&ROW()-1))

This is the VBA code (not working):
Sub Totals()
Dim lines, NumRow As Integer

lines = LastRowIndex(ActiveSheet, 4)
NumRow = 8

While NumRow <= lines
If Cells(NumRow, 4) = "Total" Then
Range("F" & NumRow & ":Q" & NumRow).Formula = "=SUM(INDIRECT(CHAR(COLUMN()+64)&ROW()-INDIRECT(""C""&ROW())):INDIRECT(CHAR(COLUMN()+64)&ROW()-1))"
Else
NumRow = NumRow + 1
End If
Wend
 
Sort by date Sort by votes
Hi Andrew,

The column C counts the number of rows to be included on the partial totals and the LastRowIndex gives me the last row containing data, so when looking up for "Totals" on column D, I don't need to search on the entire column, making my loop (while) shorter.

Please find below an example of the spreadsheet and thank you for your reply.

Cheers!



Excel 2012
ABCDEFGHIJKLMNOPQR
Project Budget
Project Name:
Nickname:
Reference:
Project Expenses
Artwork
Manual - User
Manual - Service
Labels
Carton
Photography
Total
Gas Certification
Specification
Lab Testing
Gasmark
Total
Electrical Certification
Total

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #BFBFBF, align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]27[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #BFBFBF, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #BFBFBF, align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #BFBFBF, align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #BFBFBF, align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #BFBFBF, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #BFBFBF"]ID[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]Jan-13[/TD]
[TD="align: right"]Feb-13[/TD]
[TD="align: right"]Mar-13[/TD]
[TD="align: right"]Apr-13[/TD]
[TD="align: right"]May-13[/TD]
[TD="align: right"]Jun-13[/TD]
[TD="align: right"]Jul-13[/TD]
[TD="align: right"]Aug-13[/TD]
[TD="align: right"]Sep-13[/TD]
[TD="align: right"]Oct-13[/TD]
[TD="align: right"]Nov-13[/TD]
[TD="align: right"]Dec-13[/TD]
[TD="align: right"] Total [/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #BFBFBF, align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ - [/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #BFBFBF"]Header[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ - [/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #BFBFBF"]Artwork[/TD]

[TD="align: right"][/TD]
[TD="align: right"] $ 100.00 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 100.00 [/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #BFBFBF"]Artwork[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 255.00 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 255.00 [/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #BFBFBF"]Artwork[/TD]

[TD="align: right"][/TD]
[TD="align: right"] $ 20.00 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 151.00 [/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 255.00 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 426.00 [/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #BFBFBF"]Artwork[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 255.00 [/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 255.00 [/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #BFBFBF"]Artwork[/TD]

[TD="align: right"][/TD]
[TD="align: right"] $ 1,000.00 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #BFBFBF"]Header[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"] $ 1,120.00 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 1,120.00 [/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #BFBFBF, align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ - [/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #BFBFBF"]Header[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ - [/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #BFBFBF"]Cert_Gas[/TD]

[TD="align: right"][/TD]
[TD="align: right"] $ 10.00 [/TD]
[TD="align: right"] $ 4.00 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 14.00 [/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #BFBFBF"]Cert_Gas[/TD]

[TD="align: right"][/TD]
[TD="align: right"] $ 5.00 [/TD]
[TD="align: right"] $ 2.00 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 7.00 [/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #BFBFBF"]Cert_Gas[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 3.00 [/TD]
[TD="align: right"] $ 1.00 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #BFBFBF"]Cert_Gas[/TD]

[TD="align: right"][/TD]
[TD="align: right"] $ 1,700.00 [/TD]
[TD="align: right"] $ 10.00 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #BFBFBF"]Header[/TD]
[TD="align: right"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"] $ 1,718.00 [/TD]
[TD="align: right"] $ 17.00 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 1,735.00 [/TD]

[TD="align: center"]23[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #BFBFBF, align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ - [/TD]

[TD="align: center"]24[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #BFBFBF"]Header[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ - [/TD]

[TD="align: center"]25[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #BFBFBF"]Cert - Elec[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 10.00 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 10.00 [/TD]

[TD="align: center"]26[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #BFBFBF"]Cert - Elec[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 10.00 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 10.00 [/TD]

[TD="align: center"]27[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #BFBFBF"]Header[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ - [/TD]

[TD="align: center"]28[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #BFBFBF, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ - [/TD]

[TD="align: center"]29[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #BFBFBF, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ - [/TD]

[TD="align: center"]30[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #BFBFBF, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ - [/TD]

[TD="align: center"]31[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #BFBFBF, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ - [/TD]

[TD="align: center"]32[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #BFBFBF, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ - [/TD]

</tbody>
template​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]R8[/TH]
[TD="align: left"]=SUM(F8:Q8)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]R9[/TH]
[TD="align: left"]=SUM(F9:Q9)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]R10[/TH]
[TD="align: left"]=SUM(F10:Q10)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]R11[/TH]
[TD="align: left"]=SUM(F11:Q11)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]R12[/TH]
[TD="align: left"]=SUM(F12:Q12)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]R13[/TH]
[TD="align: left"]=SUM(F13:Q13)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F15[/TH]
[TD="align: left"]=SUM(INDIRECT(CHAR(COLUMN()+64)&ROW()-INDIRECT("C"&ROW())):INDIRECT(CHAR(COLUMN()+64)&ROW()-1))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]R15[/TH]
[TD="align: left"]=SUM(F15:Q15)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]R16[/TH]
[TD="align: left"]=SUM(F16:Q16)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]R17[/TH]
[TD="align: left"]=SUM(F17:Q17)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]R18[/TH]
[TD="align: left"]=SUM(F18:Q18)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]R19[/TH]
[TD="align: left"]=SUM(F19:Q19)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F22[/TH]
[TD="align: left"]=SUM(INDIRECT(CHAR(COLUMN()+64)&ROW()-INDIRECT("C"&ROW())):INDIRECT(CHAR(COLUMN()+64)&ROW()-1))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G22[/TH]
[TD="align: left"]=IF(INDIRECT("D"&ROW())="Total",SUM(INDIRECT(CHAR(COLUMN()+64)&ROW()-INDIRECT("C"&ROW())):INDIRECT(CHAR(COLUMN()+64)&ROW()-1)),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C8[/TH]
[TD="align: left"]=IF(D9="Total",COUNTIF($B$9:B9,B8),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C9[/TH]
[TD="align: left"]=IF(D9="Total",COUNTIF($B$9:B9,B8),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C10[/TH]
[TD="align: left"]=IF(D10="Total",COUNTIF($B$9:B10,B9),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C11[/TH]
[TD="align: left"]=IF(D11="Total",COUNTIF($B$9:B11,B10),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C12[/TH]
[TD="align: left"]=IF(D12="Total",COUNTIF($B$9:B12,B11),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C13[/TH]
[TD="align: left"]=IF(D13="Total",COUNTIF($B$9:B13,B12),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C14[/TH]
[TD="align: left"]=IF(D14="Total",COUNTIF($B$9:B14,B13),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C15[/TH]
[TD="align: left"]=IF(D15="Total",COUNTIF($B$9:B15,B14),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C16[/TH]
[TD="align: left"]=IF(D16="Total",COUNTIF($B$9:B16,B15),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C17[/TH]
[TD="align: left"]=IF(D17="Total",COUNTIF($B$9:B17,B16),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C18[/TH]
[TD="align: left"]=IF(D18="Total",COUNTIF($B$9:B18,B17),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C19[/TH]
[TD="align: left"]=IF(D19="Total",COUNTIF($B$9:B19,B18),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C20[/TH]
[TD="align: left"]=IF(D20="Total",COUNTIF($B$9:B20,B19),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C21[/TH]
[TD="align: left"]=IF(D21="Total",COUNTIF($B$9:B21,B20),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C22[/TH]
[TD="align: left"]=IF(D22="Total",COUNTIF($B$9:B22,B21),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C23[/TH]
[TD="align: left"]=IF(D23="Total",COUNTIF($B$9:B23,B22),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C24[/TH]
[TD="align: left"]=IF(D24="Total",COUNTIF($B$9:B24,B23),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C25[/TH]
[TD="align: left"]=IF(D25="Total",COUNTIF($B$9:B25,B24),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C26[/TH]
[TD="align: left"]=IF(D26="Total",COUNTIF($B$9:B26,B25),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C27[/TH]
[TD="align: left"]=IF(D27="Total",COUNTIF($B$9:B27,B26),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]R22[/TH]
[TD="align: left"]=SUM(F22:Q22)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]R23[/TH]
[TD="align: left"]=SUM(F23:Q23)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]R24[/TH]
[TD="align: left"]=SUM(F24:Q24)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]R25[/TH]
[TD="align: left"]=SUM(F25:Q25)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]R26[/TH]
[TD="align: left"]=SUM(F26:Q26)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]R27[/TH]
[TD="align: left"]=SUM(F27:Q27)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]R28[/TH]
[TD="align: left"]=SUM(F28:Q28)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]R29[/TH]
[TD="align: left"]=SUM(F29:Q29)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]R30[/TH]
[TD="align: left"]=SUM(F30:Q30)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]R31[/TH]
[TD="align: left"]=SUM(F31:Q31)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]R32[/TH]
[TD="align: left"]=SUM(F32:Q32)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Hi Andrew,

I just tried again to run the macro and I could populate the row 15 with the formula, but then the process got stuck in a infinite loop after that. Do you believe that I am having problems when I compare If Cells(NumRow, 4) = "Total" Then to an empty cell? row 16 for example.

Regards.
 
Upvote 0
I just noticed that I forgot to increment the variable NumRow inside the if. I had just done it for the else part. (NumRow = NumRow + 1)

All good now! Thanks a lot!
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
How do I sum without knowing the range?
Replies
1
Views
388
sGraham24
S
JenniferMurphy
  • Solved
How to calculate a moving count?
2
Replies
15
Views
647
JenniferMurphy
JenniferMurphy
J
  • Solved
Excel VBA Macro to Auto Insert row and insert Total and sum
2
Replies
10
Views
380
maxpit
maxpit
C
  • Solved
Trouble with vba 'loop' finding last row
Replies
5
Views
1K
CaptainGravyBum
C
E
  • Solved
VBA to insert formula into cell
Replies
2
Views
279
EssKayKay
E

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top