CODEWITHSUNDEEP
excelvba
FatBoySlim7
FatBoySlim7

Reputation: 232

Doing SUM in EXCEL VBA on variable columns

I'm working on a report in Excel, actually building it in VB6 to be generated in excel. As can be seen in the picture below, I have 2 columns, BOUGHT and SOLD. What I' trying to do is take the TOTALS of both and place them in the TOTAL row.

So I'm trying with code like this, basically first specify where I want to put the value

xlrow= 14
xlCol=2
ActiveCell(xlRow, xlCol) = (ActiveCell.FormulaR1C1 = "=sum(B6:B12)")

' I tried this but it gave me a value of FALSE, not sure how to go about it Basically I want to go into the BOUGHT total cell, take the sum of A,B,C,D,Other,None,Unknown and have it walk to next cell and take total of SOLD:

Have it in a loop so that it goes all the way to the end of record count (See below)

I populate the excel Headings (Packages, etc) with a recordset, and as I'm trying to make it dynamic (only do the total for however many columns I have and stop at the end). So this is what I'm trying to build

xlrow=14
xlcol=2
rCount=(g_RS.recordcount *2) 'because I have two columns (Bought,sold) for each Heading
dim i as integer
for i = 1 to rCount -1
     ActiveCell(xlRow, xlCol) = (ActiveCell.FormulaR1C1 = "=sum(B6:B12)")
     xlcol=xlcol+1
next

enter image description here

Upvotes: 1

Views: 290

Answers (1)

PeterT
PeterT

Reputation: 8557

In order to help you gain an idea of how to code this more efficiently, here is an example. You can extend this through the rest of your code, but I believe it works for your situation.

You can certainly use the R1C1 notation for building a formula, but you have to properly include all the parts to create a proper reference. Use a Debug.Print on the string to see what it looks like if you need to.

Added border formatting

Option Explicit

Sub GenerateTotals()
    Dim ws As Worksheet
    Dim dataRowStart As Long
    Dim dataRowEnd As Long
    Dim totalsRow As Long
    Dim dataColumnStart As Long
    Dim columnCount As Long
    Dim dataRangeR1C1 As String
    Dim i As Long

    '--- set these values specifically, or you can calculate them
    Set ws = ActiveSheet
    dataRowStart = 6
    dataRowEnd = 12
    totalsRow = 14
    dataColumnStart = 2
    columnCount = 2
    'countCount = (g_RS.RecordCount * 2)

    For i = dataColumnStart To (dataColumnStart + columnCount - 1)
        dataRangeR1C1 = "R" & dataRowStart & "C" & i & ":R" & dataRowEnd & "C" & i
        With ws.Cells(totalsRow, i)
            .FormulaR1C1 = "=SUM(" & dataRangeR1C1 & ")"
            .Borders(xlEdgeTop).LineStyle = xlContinuous
            .Borders(xlEdgeTop).ColorIndex = xlAutomatic
            .Borders(xlEdgeTop).TintAndShade = 0
            .Borders(xlEdgeTop).Weight = xlThin
            .Borders(xlEdgeBottom).LineStyle = xlContinuous
            .Borders(xlEdgeBottom).ColorIndex = xlAutomatic
            .Borders(xlEdgeBottom).TintAndShade = 0
            .Borders(xlEdgeBottom).Weight = xlThick
        End With
    Next i

    Set ws = Nothing
End Sub

Upvotes: 1

Related Questions