PDA

View Full Version : VBA Looking for a Better solution than iCol for next loop



Steve Belsch
02-05-2020, 09:39 AM
Hi VBA experts,

I have VBA code uses For iCol, Next iCol to sum totals across 73 columns. Here is the code:

i = 3
J = i

Range("A" & (i + 6)) = "Subtotal " & Range("A" & (i + 6)).Value
For iCol = 13 To 73 'Sets which columns to Subtotal
Range(Cells(i + 6, 13), Cells(i + 6, 73)).FormulaR1C1 = "=SUBTOTAL(9,R" & J & "C:R[-1]C)"
Next iCol

Is there a more efficient way to do this calculation across 73 columns?

Thanks for any ideas.

Steve

p45cal
02-05-2020, 12:32 PM
This has been pointed out to you twice before:
the ps in http://www.vbaexpress.com/forum/showthread.php?66360-VBA-Code-to-add-groupings-to-subtotals&p=397133&viewfull=1#post397133
and
http://www.vbaexpress.com/forum/showthread.php?66597-VBA-Copy-Paste-Formula&p=398305&viewfull=1#post398305
you don't need the For Next loop at all:
i = 3
J = i

Range("A" & (i + 6)) = "Subtotal " & Range("A" & (i + 6)).Value
Range(Cells(i + 6, 13), Cells(i + 6, 73)).FormulaR1C1 = "=SUBTOTAL(9,R" & J & "C:R[-1]C)"

jolivanes
02-07-2020, 04:46 PM
@p45cal
I hope you don't expect a thank you note.
http://www.vbaexpress.com/forum/showthread.php?66692-Renaming-Worksheets-Using-VBA

p45cal
02-07-2020, 05:30 PM
@p45cal
I hope you don't expect a thank you note.
I don't mind too much because, for the most part, Steve's been quite good at acknowledging help.