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?
ColEndneeds 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" 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
FormulaR1C1property to determine your formula by numbers, you will able to create an array. For example, instead of referring to cell
E1in the formula, you would refer to cell
R1C5. Also, you need to change
CStr(i)in the formulas to be able to concatenate strings.