string variable referencing range not being evaluated within workbookfunction SUM

0
votes

I am trying to figure out the correct syntax so that my String variable colLetter will be evaluated within the SUM workbookfunction instead of being read as the literal text "colLetter".

colLetter stores Strings within a loop for each column range: "A:A", then "B:B", "C:C" and so on...

I have a function Col_Letter(integer) As String that takes an integer and returns the String of the column letter in excel that integer corresponds to. Example: Col_Letter(3) returns "C"

Right now my loop looks like so:

For c = 3 To lastColumn

    Dim colLetter As String
    colLetter = Col_Letter(c) & ":" & Col_Letter(c)
    Cells(lastRow + 1, c).Formula = "=SUM(colLetter)"


Next

I want the formula to return =SUM("A:A"), =SUM("B:B") etc...but it returns =SUM(colLetter) instead and thus #Value!

I can't figure out what needs to be fixed with my syntax of the sum formula. Any help would be much appreciated!

3
excelvba
Function Col_Letter(lngCol As Integer) As String Dim vArr vArr = Split(Cells(1, lngCol).Address(True, False), "$") Col_Letter = vArr(0) End Function - DHagens11

3 Answers

2
votes

Not needed if you use R1C1 reference:

Cells(lastRow + 1, 3).Resize(, lastColumn - 2).FormulaR1C1 = "=SUM(R1C:R[-1]C)"

A1 reference alternative:

Cells(lastRow + 1, 3).Resize(, lastColumn - 2).Formula = "=SUM(C1:C" & lastRow & ")"
1
votes

There is no need to get the letter of the column, use the number, it is faster.

Also if you try to sum the whole column with a formula in the same column you will get a circular reference error.

For c = 3 To lastColumn    
    Cells(lastRow + 1, c).Formula = "=SUM(" & Range(Cells(1,c),Cells(lastRow, c)).Address & ")"   

Next
0
votes

One solution is to alter your function like this:

Function Col_letter(iCol As Long) As String
    If iCol > 0 And iCol <= Columns.Count Then
        Col_letter = Evaluate("substitute(address(1, " & iCol & ", 4), ""1"", """")")
    End If
End Function