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

[Rafaa]

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

 

[Andrew Poulsom]

What's in column C? And what is the function LastRowIndex?

 

[Rafaa]

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

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1				Project Budget				27
2
3				Project Name:
4				Nickname:
5				Reference:
6
7		ID		Project Expenses		Jan-13	Feb-13	Mar-13	Apr-13	May-13	Jun-13	Jul-13	Aug-13	Sep-13	Oct-13	Nov-13	Dec-13	Total
8																		$ -
9		Header		Artwork														$ -
10		Artwork		Manual - User		$ 100.00												$ 100.00
11		Artwork		Manual - Service				$ 255.00										$ 255.00
12		Artwork		Labels		$ 20.00				$ 151.00		$ 255.00						$ 426.00
13		Artwork		Carton												$ 255.00		$ 255.00
14		Artwork		Photography		$ 1,000.00
15		Header	5	Total		$ 1,120.00												$ 1,120.00
16																		$ -
17		Header		Gas Certification														$ -
18		Cert_Gas		Specification		$ 10.00	$ 4.00											$ 14.00
19		Cert_Gas		Lab Testing		$ 5.00	$ 2.00											$ 7.00
20		Cert_Gas				$ 3.00	$ 1.00
21		Cert_Gas		Gasmark		$ 1,700.00	$ 10.00
22		Header	4	Total		$ 1,718.00	$ 17.00											$ 1,735.00
23																		$ -
24		Header		Electrical Certification														$ -
25		Cert - Elec				$ 10.00												$ 10.00
26		Cert - Elec				$ 10.00												$ 10.00
27		Header	2	Total														$ -
28																		$ -
29																		$ -
30																		$ -
31																		$ -
32																		$ -

<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>
</tbody>

template​

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

<tbody>
</tbody>

 

[Rafaa]

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.

 

[Rafaa]

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!