-
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?
-
Re: SUM of selected cell range
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?
-
Re: SUM of selected cell range
i think OP needs to do it in Access itself.
-
Re: SUM of selected cell range
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.
-
Re: SUM of selected cell range
Hi Seenu,
Sorry I did not get you. Can you please send me some sample code for this?
-
Re: SUM of selected cell range
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.
-
Re: SUM of selected cell range
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....
-
Re: SUM of selected cell range
Pls find attached here just a sample file
-
Re: SUM of selected cell range
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
-
1 Attachment(s)
Re: SUM of selected cell range
-
Re: SUM of selected cell range
nowhere it is attached, pls attach again
-
Re: SUM of selected cell range
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
-
Re: SUM of selected cell range
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?
-
Re: SUM of selected cell range
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
-
Re: SUM of selected cell range
r u running this code from excel?
-
Re: SUM of selected cell range
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
-
Re: SUM of selected cell range
@ 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
-
Re: SUM of selected cell range
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.
-
Re: SUM of selected cell range
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
missed that part, apology
-
Re: SUM of selected cell range
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
-
Re: SUM of selected cell range
What exact error are you getting?
-
Re: SUM of selected cell range
Hi Sid,
I got below error
Compile error:
Invalid or unqualified reference.
-
Re: SUM of selected cell range
Ok which line do you get the error in?
-
Re: SUM of selected cell range
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"
-
Re: SUM of selected cell range
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
-
Re: SUM of selected cell range
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
-
Re: SUM of selected cell range
may be With Block missing
Code:
With sheetname
'code
End With
r u running this code from excel or access?
-
Re: SUM of selected cell range
For i = 1 To 80
Does it mean this sum will copy in 80 columns? From column 1 till column number 80, right?
-
Re: SUM of selected cell range
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
-
1 Attachment(s)
Re: SUM of selected cell range
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.
-
Re: SUM of selected cell range
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)"
-
Re: SUM of selected cell range
try
vb Code:
objXLSheet.range(sRange).FormulaR1C1 = "=SUM(R" & startrow & "C:R[-1]C)"
-
Re: SUM of selected cell range
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....
-
Re: SUM of selected cell range
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
-
Re: SUM of selected cell range
I want to calculate total horizontally and vertically in both Currently doing only one way
-
Re: SUM of selected cell range
use the same for summing rows as you do for columns, just change the RC values to suit, loop rows rather than columns
-
Re: SUM of selected cell range
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?
-
Re: SUM of selected cell range
Quote:
Only issue is this shows the formula in the cell.
works correctly for me
-
Re: SUM of selected cell range
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
-
Re: SUM of selected cell range
Ria, the post #30 dosent help?
-
Re: SUM of selected cell range
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
-
Re: SUM of selected cell range
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
-
Re: SUM of selected cell range
Value of sRange = B11:R11
Value of sumRowEnd = 20
-
Re: SUM of selected cell range
Hi Ria,
which line gives error? can u attach the file u tried?