# How to replace "S" in a formula to be able to have a variable instead of this letter S?

I think the question is simple, the answer maybe not.

``````Sheet1.Cells().Value = "=SUM(E" & i & ":S" & i & ")/" & n
``````

In short, I would like to replace the letter S with a variable (from E to AB, I don't stop to Z!) to be able to put this simple formula in a serie of cells. When writing this question, I was thinking of using an array. I think I will have a look in this direction. But, if you have a more simple answer, do not hesitate. Thank you.

To me the obvious answer is:

``````Dim ColEnd As String

ColEnd = "X"    ' X is anything between E and AB
Sheet1.Cells().Value = "=SUM(E" & i & ":" & ColEnd & i & ")/" & n
``````

Am I missing something?

If `ColEnd` needs to be a number, this will perform the conversion:

``````Public Function ColNumToCode(ByVal ColNum As Long) As String

Dim ColCode As String
Dim PartNum As Long

'   2004?  Originally written for two character codes for Excel 2003.
'          Date not recorded.
'  3Feb12  Adapted to handle three character codes.
'  5Jul20  I coded the routine below before I properly understood the Range
'          property Address. Once I understood it, I assumed the following
'          would be faster:
'          However, when I timed the two approaches, I found:
'            * the Address property technique was 6.0 times slower
'              than the code below for columns 1 to 26 (A to Z)
'            * the Address property technique was 3.8 times slower
'              than the code below for columns 27 to 702 (AA to ZZ)
'            * the Address property technique was 2.9 times slower
'              than the code below for columns 703 to 16834 (AAA to XFD)

If ColNum = 0 Then
Debug.Assert False
ColNumToCode = "0"
Else
ColCode = ""
Do While ColNum > 0
PartNum = (ColNum - 1) Mod 26
ColCode = Chr(65 + PartNum) & ColCode
ColNum = (ColNum - PartNum - 1) \ 26
Loop
End If

ColNumToCode = ColCode

End Function
``````

Use the `FormulaR1C1` property to determine your formula by numbers, you will able to create an array. For example, instead of referring to cell `E1` in the formula, you would refer to cell `R1C5`. Also, you need to change `i` to `CStr(i)` in the formulas to be able to concatenate strings.