SUM of selected cell range

[riaarora]

Hi

I need to calculate SUM of selected cell range e.g.

PB YU Bud PBud Actual TC
Sam 120 130 190 130 150
James 130 150 110 120 130
Raj 150 120 120 100 120
=================================
Total 400 400 420 350 400

How to calculate the total using Ms Access VBA? Also to how draw "=====" line?

[koolsid]

Welcome to the forums Ria

Are you trying to automate Excel from Access VBA? If yes, then how are you binding with Excel? LateBinding or EarlyBinding?

[seenu_1st]

i think OP needs to do it in Access itself.

[riaarora]

Hi Sid,

Yes, I'm trying to automate Excel from Access VBA. I have all the data in Access and executing the SQL in Access writing data in particular cells and after that I need to sum these cells. And these number of columns need to dynamic e.g. it can be =SUM(B3:B7) or =SUM(B3:B10) depends on number of records fetched by SQL.

[riaarora]

Hi Seenu,

Sorry I did not get you. Can you please send me some sample code for this?

[seenu_1st]

Ria, i never worked with access vba, can u attach a sample file of access and explain where do u need to show the sum of values.

[riaarora]

Hi

I'm using following method to populate the data in Excel Sheet

    For count = 1 To recount
        ColumnStart = 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![Banker]
        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![RevenueYTD]
        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![AnnuRevenue]
        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![RevenueTCY]
        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![RevenuePFY]
        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![AnnuVsBud]
        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![AnnuVsPFYRev]
        ColumnStart = ColumnStart + 1
        ..............
        ..............
        ..............
        ..............
        ..............

        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![AnnuActRatio]

        If rsTmp.EOF Then Exit For
        RowStart = RowStart + 1
        rsTmp.MoveNext
    Next count

Data will be populated in around 80 columns and I need the SUM for all these columns. I don't want to SUM one by one cell using below function

.Range("B11").Formula = "=Sum(B6:B10)"

Pls let me the better way to do this....

[riaarora]

Pls find attached here just a sample file

[seenu_1st]

Ria, nothing was attached, u can try this code to sum, 1 to 80 is column

Private Sub CommandButton1_Click()
Dim LastRow As Long
For i = 1 To 80
    LastRow = Cells(Rows.Count, i).End(xlUp).Row
    Cells(LastRow + 1, i) = WorksheetFunction.Sum(Range(Cells(1, i), Cells(LastRow, i)))
Next
End Sub

[riaarora]

Pls find attached file

[seenu_1st]

nowhere it is attached, pls attach again

[koolsid]

Nice one Seenu Two suggestions.

1) When working with sheets fully `Qualify` your objects else the code will work with the "Active" sheet
2) The above is also true and crucial when finding last row. See the EXTRA DOT before Rows.Count. This usually causes a problem when you open xls file in compatibility mode and don't use the EXTRA DOT.

Something like this

    With objXLSheet
        LastRow = .Cells(.Rows.Count, i).End(xlUp).Row
    End With

[seenu_1st]

sidhu, actuly i just made it for an example purpose only, anyhw u pointed good things.

Ria, do u need to sum the Rows or Columns?

[riaarora]

Hi Sid / Seenu,

I got below error

Compile error:
Invalid or unqualified reference.

    For i = 1 To 80
        RowEnd = .Cells(.Rows.count, i).End(xlUp).Row
        Cells(RowEnd + 1, i) = WorksheetFunction.SUM(range(Cells(1, i), Cells(RowEnd, i)))
    Next

[seenu_1st]

r u running this code from excel?

[koolsid]

Ria, I don't see the whole code but my guess is that you have not specified the sheet.

    With objXLSheet
        For i = 1 To 80
            RowEnd = .Cells(.Rows.count, i).End(xlUp).Row
            .Cells(RowEnd + 1, i) = WorksheetFunction.SUM(.range(Cells(1, i), .Cells(RowEnd, i)))
        Next
    End With

[westconn1]

@ sid
wouldn't he want to keep all the totals on the same row?

could always put the formula into the cells, to allow sheet editing later

[koolsid]

Yes you are right on both the accounts.

I already thought of the first part but didn't mention it as I was waiting for her to get back first.

Regarding the second (using Formulas) that is again up to her but makes sense if she wants to edit the sheet later manually.

[westconn1]

Also to how draw "=====" line?

can either put into cells on row above totals as text, or set top border of range of cells containing totals

for her to

missed that part, apology

[riaarora]

Still getting error. Please find below the code I'm using

Dim TempString As String

    For count = 1 To recount
        ColumnStart = 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![Banker]
        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![RevenueYTD]
        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![AnnuRevenue]
        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![RevenueTCY]
        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![RevenuePFY]
        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![AnnuVsBud]
        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![AnnuVsPFYRev]
        ColumnStart = ColumnStart + 1
        ..............
        ..............
        ..............
        ..............
        ..............

        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![AnnuActRatio]

        If rsTmp.EOF Then Exit For
        RowStart = RowStart + 1
        rsTmp.MoveNext
    Next count

    rsTmp.Close
    Set rsTmp = Nothing
    
    'For double top line and single bottom line for Subtotal
    TempString = "A" & RowStart & ":" & "T" & RowStart
    boldFontSize (TempString)
    applyDoubleTotalLine (TempString)
   
'Getting Error over here
******Error*************
Compile error:
Invalid or unqualified reference.
************************
    For i = 1 To 80
        RowEnd = .Cells(.Rows.count, i).End(xlUp).Row
        Cells(RowEnd + 1, i) = WorksheetFunction.SUM(range(Cells(1, i), Cells(RowEnd, i)))
    Next

I tried below code for sum, that works but this always calculate from R2 till last line

objXLSheet.range(strRange).FormulaR1C1 = "=SUM(R2C:R[-1]C)"

I want to chnage this to something like below

objXLSheet.range(strRange).FormulaR1C1 = "=SUM(" & TempString & ":R[-1]C)"

Please help

[koolsid]

What exact error are you getting?

[riaarora]

Hi Sid,

I got below error

Compile error:
Invalid or unqualified reference.

[koolsid]

Ok which line do you get the error in?

[westconn1]

I tried below code for sum, that works but this always calculate from R2 till last line

so what number of rows do you want to sum?

objXLSheet.range(strRange).FormulaR1C1 = "=SUM(" & TempString & ":R[-1]C)"

what value has tempstring? should be something like "R" & startrow & "C"

[riaarora]

Hi Sid,

Getting error at 2nd line

.Cells(.Rows highlighted

and errors is
Complie Error:
Invalid or unqualified reference

    For i = 1 To 80
        RowEnd = .Cells(.Rows.count, i).End(xlUp).Row 
        Cells(RowEnd + 1, i) = WorksheetFunction.SUM(range(Cells(1, i), Cells(RowEnd, i)))
    Next

[riaarora]

Hi Sid,

Can you please help to explain if I need to calculated sum from different range e.g. Row 15 to Row 20, and later using same code Row 23 to 28 and so on.

Thanks a lot for the help

[seenu_1st]

may be With Block missing

With sheetname
'code
End With

r u running this code from excel or access?

[riaarora]

For i = 1 To 80

Does it mean this sum will copy in 80 columns? From column 1 till column number 80, right?

[riaarora]

Hi Pete,

TempString return the value: B8:T8

TempString = "B" & RowStart & ":" & "T" & RowStart

depending where Sum need to calculated.

Later in the code TempString will return

B28:J28

[seenu_1st]

For i = 1 To 80

Does it mean this sum will copy in 80 columns? From column 1 till column number 80, right?

yes, see the attached excel file for example.

[riaarora]

so what number of rows do you want to sum?


what value has tempstring? should be something like "R" & startrow & "C"

Getting error (Expected: End of Statement) for following

objXLSheet.range(sRange).FormulaR1C1 = "=SUM(" "R" & startrow & "C" ":R[-1]C)"

[westconn1]

try

objXLSheet.range(sRange).FormulaR1C1 = "=SUM(R" & startrow & "C:R[-1]C)"

[riaarora]

Hi Pete,

Excellent! That works. One more question, how to get the total for range on columns?

PB YU Bud PBud Actual TC Total
Sam 2 3 1 5 4 15
Bob 1 0 5 0 0 6
Raj 1 0 2 0 0 3
==========================
Total 4 3 8 5 4 24

Thanks a lot for the help....

[westconn1]

One more question, how to get the total for range on columns?

i don't understand, i thought that was what you already did for each section
if you sum the columns now, it will include the sum for each section as well as the sum of all rows

[riaarora]

I want to calculate total horizontally and vertically in both Currently doing only one way

[westconn1]

use the same for summing rows as you do for columns, just change the RC values to suit, loop rows rather than columns

[riaarora]

Thanks a lot Pete. This works fine.

Sub calculateRowSubtotal(sRange As String, sumRowStart As Integer)
    With objXLSheet.range(sRange)
        objXLSheet.range(sRange).FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
    End With
End Sub

Only issue is this shows the formula in the cell. What about if want to show actual number only instead of formula in cells?

[westconn1]

Only issue is this shows the formula in the cell.

works correctly for me

[riaarora]

It works fine for me as well Issue is I don't want SUM(C10:C20) in cell I want only value e.g. 10902

[seenu_1st]

Ria, the post #30 dosent help?