CODEWITHSUNDEEP
vbaexcel
user1624926
user1624926

Reputation: 451

Sum Variable columns

In the following line of code I'm trying to sum a variable range (Column U). Can you please let me know what I'm doing wrong within my code (any help is appreciated):

Line of code with issue:

Sheets(gcsReportSheetName).Cells(LCounter, 3).FormulaR1C1 = "=sum(" & rng.Value & "!" & Lsumcolumn & ":" & Lsumcolumn & ")"

Full code.

Sub Report()

Dim rng As Range
Dim LCounter As Long
Dim sLsheet As String
Dim Lsumcolumn As Long
Set gRwksconfigeration = Sheets("Config")
Set gRnct_Funds_2 = gRwksconfigeration.Range(CT_Funds_2)

LCounter = 3

For Each rng In gRnct_Funds_2

    Sheets(gcsReportSheetName).Cells(LCounter, 1) = rng.Value

    If rng.Value = "" Then

            Exit Sub

        Else

            Sheets(gcsReportSheetName).Cells(LCounter, 2) = Sheets(rng.Value).Cells.Find(What:="Value", After:=Cells(1, 1), LookIn:=xlValues, LookAt:= _
                    xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
                    False, SearchFormat:=False).End(xlDown).Value
            Lsumcolumn = Sheets(rng.Value).Cells.Find(What:="illiquid check", After:=Cells(1, 1), LookIn:=xlValues, LookAt:= _
                    xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
                    False, SearchFormat:=False).Column
            Sheets(gcsReportSheetName).Cells(LCounter, 3).FormulaR1C1 = "=sum(" & rng.Value & "!" & Lsumcolumn & ":" & Lsumcolumn & ")"


            LCounter = LCounter + 1

    End If

Next

End Sub

Upvotes: 0

Views: 242

Answers (1)

Gaffi
Gaffi

Reputation: 4367

Do your sheet names (rng.Value) contain spaces? If so, you'll need to add single quotes around the name, like so:

='Test Sheet'!A:A

Also, you are using R1C1 notation, which is a little different than A1 notation (see more info here). Both of the following examples should work.

Your code with R1C1 notation should look like this:

Sheets(gcsReportSheetName).Cells(LCounter, 3).FormulaR1C1 = "=sum('" & rng.Value & "'!C" & Lsumcolumn & ")"

Your code with A1 notation should look like this:

sSumColumn = Choose(Lsumcolumn,"A","B","C","D","E","F","G") ' Expand this to include all column headers.

Sheets(gcsReportSheetName).Cells(LCounter, 3).Formula = "=sum('" & rng.Value & "'!" & sSumColumn & ":" & sSumColumn & ")"

Even if your sheet names do NOT contain spaces, this syntax should still work.

One extra note about the Choose function to get the column header...

This can be a pain to write out if you are looking at a large number of columns, so I've got a function that I use to grab this value more simply (full disclosure, I grabbed this from another site on the internet, but updated it myself for work the 2007/2010/2013 versions of Excel):

Public Function ColumnLetter(ColumnNumber As Integer) As String

    If ColumnNumber > 26 Then

        If ColumnNumber > 702 Then
            ' Compatible with Excel 2007+ extension of columns
            ColumnLetter = Chr(Int((Int((ColumnNumber - 1) / 26) - 1) / 26) + 64) & _
                Chr((Int((ColumnNumber - 1) / 26) - 1) Mod 26 + 65) & _
                Chr(((ColumnNumber - 1) Mod 26) + 65)
        Else
            ' 1st character:  Subtract 1 to map the characters to 0-25,
            '                 but you don't have to remap back to 1-26
            '                 after the 'Int' operation since columns
            '                 1-26 have no prefix letter

            ' 2nd character:  Subtract 1 to map the characters to 0-25,
            '                 but then must remap back to 1-26 after
            '                 the 'Mod' operation by adding 1 back in
            '                 (included in the '65')

            ColumnLetter = Chr(Int((ColumnNumber - 1) / 26) + 64) & _
                Chr(((ColumnNumber - 1) Mod 26) + 65)
        End If
    Else
        ' Columns A-Z
        ColumnLetter = Chr(ColumnNumber + 64)
    End If
End Function

Instead of the Choose formula above, you can use sSumColumn = ColumnLetter(Lsumcolumn).

Upvotes: 2

Related Questions