PDA

View Full Version : Loop



Staal
11-13-2010, 04:21 AM
Hi,

I am quite new to VBA coding and have some problems finding an easier way to write the following:

Range("S5").Select
ActiveCell.FormulaR1C1 = "=R[0]C[-13]"
Range("S13").Select
ActiveCell.FormulaR1C1 = "=R[-7]C[-13]"
Range("S21").Select
ActiveCell.FormulaR1C1 = "=R[-14]C[-13]"
Range("S29").Select
ActiveCell.FormulaR1C1 = "=R[-21]C[-13]"
Range("S37").Select
ActiveCell.FormulaR1C1 = "=R[-28]C[-13]"
Range("S45").Select
ActiveCell.FormulaR1C1 = "=R[-35]C[-13]"

Any help is much appreciated.

Best regards,

Soren

shrivallabha
11-13-2010, 04:41 AM
You can use for next loop. There will be some articles on this forum and loads of example. Looking at your current requirement

For i = 5 To 45 Step 8
ActiveCell.FormulaR1C1 = "=R[0]C[-13]"
Next i

Hth,

Staal
11-13-2010, 04:49 AM
Thanks a lot for your help...

Regards,

Soren

Bob Phillips
11-13-2010, 04:51 AM
Dim i As Long
With Range("S5")

For i = 0 To 40 Step 8
.Offset(i, 0).FormulaR1C1 = "=R[-" & (i \ 8) * 7 & "]C[-13]"
Next i
End With

macropod
11-13-2010, 04:51 AM
Hi Soren,

Try:Sub Demo()
Dim i As Integer
With Range("S5")
For i = 0 To 5
.Offset(i * 8, 0).FormulaR1C1 = "=R[" & -i * 7 & "]C[-13]"
Next i
End With
End Sub