Loop

[Staal]

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]

You can use for next loop. There will be some articles on this forum and loads of example. Looking at your current requirement

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

Hth,

[Staal]

Thanks a lot for your help...

Regards,

Soren

[Bob Phillips]

[vba]

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
[/vba]

[macropod]

Hi Soren,

Try:[VBA]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[/VBA]