S

Hot Topic

Open an excel file
Just gotta have a moan
Problem with MS Updates on New Dell
Adding windows forms
ReadAll reading in first 254 characters only. Help
winHTTP access to secure ASP site?
Strange Error
Excel macro not able to SetSourceData for chart
Form Requery
MS Access key problem

Microsoft ISV

Automaticly updating charts in excel
Need recommendation on Office/VBA/.NET/C# solution (cross-posting from the VSTO Forum)
Access 2003 - Retrieving computer information
Automaticly updating charts in excel
Can anyone download VS Team Edition?
Excel macro to send email not running
Needed urgent to download Windows live messenger
Linking Outlook to Excel
What is ISV
Put E-mail In From Field

S

Hi there,

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

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.

Hope somebody can help.

Thanks very much!

Regards,

Patrick Briggs,
Pasadena, CA

Answer this question

S

  • Neil_Trainer1

    Related to this question, I wanted to share some code that does a little bit more than just get the totals on a varying number of rows.
    ===========================
    SUMMARY:
    I do 4 things to two columns of data. These two columns are Gift Size Amounts and # of Gifts for each amount.
    1) Get a total for the first column that adjusts to the differing number of rows for a given active worksheet.
    2) Get a total for the 2nd column .....
    3) Get a percentage for each row off the total # of gifts
    4) Calculate which rows are greater than or equal to 5% and highlight the row for three columns in blue
    I have one sub that calls 4 other subprocedures:
    ======================
    Sub FormatGDist()
    Call SumVarRangeA
    Call SumVarRangeB
    Call PercentageGiftsCateg
    Call TopPercentage
    End Sub
    ======================((this one gets called by the first two subs))
    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
    ======================
    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
    'MsgBox x & " " & y
    BegRng = "A7"
    EndRngA = "A" & x
    'MsgBox BegRng
    'MsgBox EndRngA
    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
    ======================
    Sub SumVarRangeB()
    Dim x As Integer
    Dim y As Integer
    Dim BegRng As Variant
    Dim EndRngB As Variant
    Dim CountRng As Variant

    x = LastCell(ActiveSheet).Row
    y = LastCell(ActiveSheet).Column
    'MsgBox x & " " & y
    BegRng = "B7"
    EndRngB = "B" & x
    'MsgBox BegRng
    'MsgBox EndRngB
    CountRng = ActiveSheet.Range(BegRng & ":" & EndRngB).count
    'MsgBox CountRng
    ActiveSheet.Range(EndRngB).Select
    'ActiveCell.Offset(2, 0).Select
    ActiveCell.FormulaR1C1 = "=SUM(R[" & "-" & CountRng + 1 & "]C:R[-1]C)"
    ActiveSheet.Columns("B:B").Select
    Selection.NumberFormat = "#,##0"
    End Sub
    ======================
    Sub PercentageGiftsCateg()
    Dim x As Integer
    Dim y As Integer
    Dim BegRng As Variant
    Dim EndRngB As Variant
    Dim CountRng As Variant
    Dim CopyRng As Variant
    Dim quotes As String

    x = LastCell(ActiveSheet).Row
    y = LastCell(ActiveSheet).Column
    quotes = Chr(34)
    BegRng = "B7"
    EndRngB = "B" & x
    ActiveSheet.Range("C7").Select
    CountRng = ActiveSheet.Range(BegRng & ":" & EndRngB).count
    ActiveCell.FormulaR1C1 = "=RC[-1]" & "/" & "R" & x & "C2"
    Selection.NumberFormat = "0.00%"
    ActiveCell.Copy
    CopyRng = "C8" & ":" & "C" & (x - 2)
    ActiveSheet.Range(CopyRng).Select
    ActiveSheet.Paste
    Selection.NumberFormat = "0.00%"
    End Sub
    ======================
    Sub TopPercentage()
    Dim x As Integer
    Dim y As Integer
    Dim Beg As Integer
    Dim BegRng As Variant
    Dim CurRng As Variant
    Dim EndRngB As Variant

    Dim count As Integer
    Dim counter As Integer

    x = LastCell(ActiveSheet).Row
    y = LastCell(ActiveSheet).Column
    Beg = 7
    'BegRng = "A7:C7"
    'EndRngB = "A" & x & ":" & "C" & x
    For count = Beg To (x - 2)
    'counter = count + 1
    CurRng = "A" & count & ":" & "C" & count
    If ActiveSheet.Range("C" & count).Value >= 0.05 Then
    Range(CurRng).Select
    With Selection.Interior
    .ColorIndex = 37
    .Pattern = xlSolid
    End With
    End If
    Next count
    End Sub
    ======================
    I hope this is helpful to somebody. It was my first attempt at putting VBA code to immediate use at my job.
    Regards,
    Patrick Briggs,
    Pasadena, CA

  • NikosG

    This will insert the formula:

    ActiveSheet.Cells(x + 1, y) = "=sum(" & BegRng & ":" & EndRng & ")"
  • S
Answer this question