Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Summing on varying number of rows in Excel using VBA

  Asked By: Jesse    Date: Jan 07    Category: MS Office    Views: 6877
  

I'm trying to do a sum on a varying number of rows in a column. I have
to do this for a number of worksheets but I'm focusing on getting the
code right for just one of the worksheets right now (the active one).
At the moment the starting row for all data in these worksheets is Row
7.

Example:

Sheet1

row 7 - 2
row 8 - 10
row 9 - 5

Sheet2

row 7 - 8
row 8 - 12
row 9 - 14
row 10 - 20
row 11 - 4
row 12 - 6

The module is something I picked up here:

http://www.beyondtechnology.com/geeks012.shtml
<http://www.beyondtechnology.com/geeks012.shtml>

It looks like this:

Option Explicit

Function LastCell(WS As Worksheet) As Range
Dim LastRow&, LastCol%

' Error-handling is here in case there is not any
' data in the worksheet

On Error Resume Next

With WS

' Find the last real row

LastRow& = .Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row

' Find the last real column

LastCol% = .Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column

End With

' Finally, initialize a Range object variable for
' the last populated row.

Set LastCell = WS.Cells(LastRow&, LastCol%)

End Function

The subprocedure that calls this procedure (pardon me if I'm getting my
terminology mixed up - I'm new to using VBA) is:

Sub SumVarRange()
Dim x As Integer
Dim y As Integer
Dim BegRng As Variant
Dim EndRng As Variant
Dim CountRng As Variant

x = LastCell(ActiveSheet).Row
y = LastCell(ActiveSheet).Column
'MsgBox x & " " & y
BegRng = "A7"
EndRng = "A" & x
MsgBox BegRng
MsgBox EndRng
CountRng = ActiveSheet.Range(BegRng & ":" & EndRng).Count
MsgBox CountRng
ActiveSheet.Range(EndRng).Select
ActiveCell.Offset(2, 0).Select
ActiveCell.FormulaR1C1 = "=SUM(R[" - "& CountRng]C:R[-1]C)"
End Sub

My problem is trying to pass the count of rows between BegRng and EndRng
inclusive. I highlighted this in yellow where my passing a variant in
string failed.

1) How can I fix the above to pass the value of CountRng into the Sum
formula

2) Is there a more efficient way handling varying ranges for summing,
formatting etc?

3) Where should I be putting the above subprocedure (at least that's
what I'm calling it) - a specific worksheet object - or - in another
module?

a) I understand the idea of a module and then a sub calling it but I
haven't any idea where the best place is to put the sub procedure.

4) Maybe there's a good book on VBA for Excel that can help me get basic
syntax, terminology and a good understanding of how to work in VBA?

a) I'm looking for one that will get a newbie like me up to speed so
I'll be less timid about experimenting and not get hung up on the little
stuff.

Share: 

 

1 Answer Found

 
Answer #1    Answered By: Dot net Sachin     Answered On: Jan 07

here is the code that I got to work:

Option Explicit
________________________________________________________

Sub SumVarRangeA()
Dim x As Integer
Dim y As Integer
Dim BegRng As Variant
Dim EndRngA As Variant
Dim CountRng As Variant

x = LastCell(ActiveSheet).Row
y = LastCell(ActiveSheet).Column
BegRng = "A7"
EndRngA = "A" & x
CountRng = ActiveSheet.Range(BegRng & ":" & EndRngA).count
'MsgBox CountRng
ActiveSheet.Range(EndRngA).Select
ActiveCell.Offset(2, 0).Select
ActiveCell.FormulaR1C1 = "=SUM(R[" & "-" & CountRng + 1 &
"]C:R[-1]C)"
ActiveSheet.Columns("A:A").Select
Selection.NumberFormat = "$#,##0.00"
End Sub


The above subprocedure calls  this subprocedure:

Option Explicit
________________________________________________________________

Function LastCell(WS As Worksheet) As Range
Dim LastRow&, LastCol%

' Error-handling is here in case  there is not any
data  in the worksheet

On Error Resume Next

With WS

' Find the last real  row

LastRow& = .Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row

' Find the last real column

LastCol% = .Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column

End With

' Finally, initialize  a Range object  variable for
' the last populated row.

Set LastCell = WS.Cells(LastRow&, LastCol%)

End Function

===============================================================

It's nice to be able to give something back to forums. Hope this is
helpful to anybody that happens across this problem.

 
Didn't find what you were looking for? Find more on Summing on varying number of rows in Excel using VBA Or get search suggestion and latest updates.




Tagged: