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.

2 answers

  • answered 2020-07-04 23:17 Tony Dallimore

    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:
      '             Dim Addr As String
      '             Addr = Cells(1, ColNum).Address
      '             ColNumToCode = Mid(Addr, 2, Len(Addr) - 3)
      '          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"
        ColCode = ""
        Do While ColNum > 0
          PartNum = (ColNum - 1) Mod 26
          ColCode = Chr(65 + PartNum) & ColCode
          ColNum = (ColNum - PartNum - 1) \ 26
      End If
      ColNumToCode = ColCode
    End Function

  • answered 2020-07-05 00:12 xwhitelight

    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.