Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

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.

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.

Related Topics:

- Convert code from excel 2000 vba to excel 2003 vba
- Excel VBA to delete a row of data
- Need Excel VBA Help with deleting rows
- Cannot use the VBA add table command of MS word in MS Excel VBA
- Cannot use the VBA add table command of MS word in MS Excel VBA
- VBA code to sum
- SUM content of rows
- Insert Rows, based on data in range, using VBA
- Data Sum rows not hidded
- Excel VBA Problem in excel 2000
- how to sum the even numbers only
- sum and difference of 2 bcd numbers
- sum and Multiply number and print in decimal
- sum of series of natural numbers
- VBA to use unique password using volume serial number
- Problem using VB6 (not VBA) to put picture file into Excel
- Access 7 to Access 2003 using Excel VBA
- Closing Excel Window using VBA
- Using excel VBA to change a cell color
- Web to Excel Using VBA
- Attaching a File to an Outlook Email using VBA (in Excel)
- Using Excel VBA to chart a moving annual total
- Getting the sum and percentage in Excel coulmn
- error using secondary axis in excel chart (VBA)
- Can we make PC to Phone calls using Macro Code in Excel VBA