VBA adding a number of columns based on Variable

[bukimi]

Hello!

I'm writing a longer code, but can't find a solution to a problem. I have a variable that is a number of columns that need to be added (from 2 to 4).
The problem is that the columns to add are every 5 columns (left), that is:

'example for adding 2 columns, variable=2
    ActiveCell.FormulaR1C1 = "=RC[-5]+RC[-10]"

'example for adding 4 columns, variable=4
    ActiveCell.FormulaR1C1 = "=RC[-5]+RC[-10]+RC[-15]+RC[-20]"

Is there any way to get as many columns in a formula added as variable value? The workaround would be using "Select case" for 2-4, but some time in the future there may be more values needed, so I'm reluctant to use it that way.

 

[Misca]

Try something like:

Sub MyFormula(X As Integer)

Dim i As Integer
Dim FrmText As String

For i = 1 To X
    FrmText = FrmText + "+RC[-" & i * 5 & "]"
Next i

ActiveCell.FormulaR1C1 = "=" & FrmText

End Sub


Sub Test()

Call MyFormula(5)

End Sub

 

[bukimi]

That's a smart solution. It gives extra "+" at the start of formula, but Excel still gets it right.
For example, the result is:

=+N6+I6+D6

Should I cut it with "Right" formula from 2nd character, or would you suggest a different fix?

PS. Thank you very much!
It cuts a dozen of unnecessary lines of code!

 

[Misca]

The extra "+" doesn't matter. Some people start their formulas with that instead of typing the equals sign.