Solved

# Sum, Count and Copy

Posted on 2011-10-27
201 Views
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!

Thanks
Seamus
test.xls
0
Question by:Seamus2626

LVL 17

Expert Comment

You could do this using VBA, but why not just use the formula
``````=SUM('MI LEDGER'!F:F)
``````
in C16 and
``````=COUNT('MI LEDGER'!F:F)
``````
in D16, if I understand your requirement correctly?
0

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
Else
MsgBox "Could not find output location"
End If
End Sub
``````
0

Author Closing Comment

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

Thanks
Seamus
0

## Featured Post

### 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…