How can I change 5 to i?
For i = 5 To 58 Step 1
Range("O" & i).Formula = "=S5-SUM(I5:N5) "
Next
For next loop
-
VegasNath
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
For next loop
Nathan 
There's no place like home.....
-
HansV
- Administrator
- Posts: 78600
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: For next loop
Use
Range("O" & i).Formula = "=S" & i & "-SUM(I" & i & ":N" & i & ")"
or
Range("O" & i).FormulaR1C1 = "=RC19-SUM(RC9:RC14)"
Range("O" & i).Formula = "=S" & i & "-SUM(I" & i & ":N" & i & ")"
or
Range("O" & i).FormulaR1C1 = "=RC19-SUM(RC9:RC14)"
Best wishes,
Hans
Hans
-
mbarron
- 2StarLounger
- Posts: 112
- Joined: 25 Jan 2010, 20:19
Re: For next loop
A loop-less method
Code: Select all
Range("O5").Formula = "=S5-SUM(I5:N5)"
Range("O5").Resize(54, 1).FillDown
-
HansV
- Administrator
- Posts: 78600
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: For next loop
Even shorter:
Range("O5:O58").Formula = "=S5-SUM(I5:N5)"
Excel will automatically adjust the formula.
Range("O5:O58").Formula = "=S5-SUM(I5:N5)"
Excel will automatically adjust the formula.
Best wishes,
Hans
Hans
-
VegasNath
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Re: For next loop
Thanks for the various responses. The latter appears to be most appropriate to my needs. It also helps me (if I can ever remember) that I can use with various ranges.
Range("O5:O6, O8:O9, O11:O12").Formula = "=S5-SUM(I5:N5)"
Thanks.
Range("O5:O6, O8:O9, O11:O12").Formula = "=S5-SUM(I5:N5)"
Thanks.
Nathan There's no place like home.....