Sum, Count and Copy

Posted on 2011-10-27
Last Modified: 2012-05-12
I have attached a sheet where i would like some VBA code to start at F2 and loop until it finds the first number and then select to the end, sum that and copy the sum to the tab "Corporate Actions" and paste it into the cell next to MI Ledger, so use offset, and then repeat the same but using count and offset (0,2)

I have manually done this to highlight what i need to achieve

Ideas, suggestions and code are welcome!

Question by:Seamus2626
    LVL 17

    Expert Comment

    You could do this using VBA, but why not just use the formula

    Open in new window

    in C16 and

    Open in new window

    in D16, if I understand your requirement correctly?
    LVL 17

    Accepted Solution

    If you really do want code, as I'm guessing you may need to do similar with other accounts, this will do what you want I think.  I've tried to make it flexible so it looks up the MI LEDGER row, etc - you just have to specify where the column is you want to process.
    Public Sub SumMILedger()
        Dim shtMI As Excel.Worksheet
        Dim shtCorpActions As Excel.Worksheet
        Dim dblSum As Double
        Dim dblCount As Double
        Dim lngFoundRow As Long
        Const cstrStartCell As String = "F2"
        Const clngAmountCol As Long = 2         ' column to place the results
        Const cstrAccountName As String = "MI LEDGER"  ' row marker to place the results
        Dim rngAmt As Excel.Range
        Dim rngLast As Excel.Range
        Dim rngOutput As Excel.Range
        ' reference the sheets needed
        Set shtMI = ActiveWorkbook.Worksheets("MI LEDGER")
        Set shtCorpActions = ActiveWorkbook.Worksheets("Corporate Actions")
        ' set te amount range starting in the cell requested, down to the last value in that col
        Set rngAmt = shtMI.Range(cstrStartCell)
        Set rngLast = shtMI.Cells(shtMI.Rows.Count, rngAmt.Column).End(xlUp)
        Set rngAmt = shtMI.Range(rngAmt.Cells(1), rngLast)
        ' get the values
        dblSum = Application.WorksheetFunction.Sum(rngAmt)
        dblCount = Application.WorksheetFunction.Count(rngAmt)
        ' find where to put them
        Set rngOutput = shtCorpActions.Columns(clngAmountCol)
        lngFoundRow = 0
        On Error Resume Next
        lngFoundRow = Application.WorksheetFunction.Match(cstrAccountName, rngOutput, 0)
        On Error GoTo 0
        ' if the output account description was matched then add the values
        If lngFoundRow > 0 Then
            shtCorpActions.Cells(lngFoundRow, clngAmountCol + 1).Value = dblSum
            shtCorpActions.Cells(lngFoundRow, clngAmountCol + 2).Value = dblCount
            MsgBox "Could not find output location"
        End If
    End Sub

    Open in new window


    Author Closing Comment

    Sometimes i get VBA stuck in my head and forget theres regular solutions!!


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Suggested Solutions

    A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
    Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now