SUM of selected cell range

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?

Welcome to the forums Ria :wave:

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

i think OP needs to do it in Access itself.

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.

Hi Seenu,

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

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.

Hi

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

Code:

---

    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

Code:

---

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

---

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

Pls find attached here just a sample file

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

Code:

---

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

---

Pls find attached file

nowhere it is attached, pls attach again

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

Code:

---

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

---

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?

Hi Sid / Seenu,

I got below error

Compile error:
Invalid or unqualified reference.

Code:

---

    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

---

r u running this code from excel?

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

Code:

---

    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

---

@ 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

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.

Quote:

---

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

Quote:

---

for her to

---

missed that part, apology

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

Code:

---


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

Code:

---

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

---

I want to chnage this to something like below

Code:

---

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

---

Please help

What exact error are you getting?

Hi Sid,

I got below error

Compile error:
Invalid or unqualified reference.

Ok which line do you get the error in?

Quote:

---

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?

Quote:

---

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

---

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

Hi Sid,

Getting error at 2nd line

.Cells(.Rows highlighted

and errors is
Complie Error:
Invalid or unqualified reference

Code:

---


    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

---

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

may be With Block missing

Code:

---

With sheetname
'code
End With

---

r u running this code from excel or access?

For i = 1 To 80

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

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

Quote:

---

Originally Posted by riaarora

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.

Quote:

---

Originally Posted by westconn1

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)"

try

vb Code:

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

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....

Quote:

---

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

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

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

Thanks a lot Pete. This works fine.

Code:

---

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?

Quote:

---

Only issue is this shows the formula in the cell.

---

works correctly for me

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

Ria, the post #30 dosent help?

Quote:

---

works correctly for me

---

by correctly, i meant the sum showed in the cell, not the formula

i just pasted your code into my ide and set a range to suit

Hi Seenu,

I got below error:

Run-time error '1004':

Method 'Rows' of object '_Global' failed

Code:

---

Sub calculateSubtotalOfRows(sRange As String, sumRowEnd As Integer)
Dim i As Integer

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

End Sub

---

Value of sRange = B11:R11

Value of sumRowEnd = 20

Hi Ria,
which line gives error? can u attach the file u tried?