Macro to SumIF a reference Quantity

**dirock** · 09-11-2012, 09:50 AM

I have a query of data that I need to reorgainize using a macro. I basically need to sumif colum L "TEAM" by referencing it's quantity in column G but the kicker is that I need an absolute value. Then, I need to copy the accounts that make up the sum and place it under the amount column D in worksheet "allocation". I need to loop this through data that will always change. The attached shows my end result better. Your help with be greatly appreciated. Thank you.

Here is what I have so far but doesn't seem to work.

Sub Option_Event_Writer()

Dim r As Long, lr As Long, n As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
For r = 3 To lr
  n = Application.CountIf(Columns(12), Cells(r, 12).Value)
  If n > 1 Then
    Cells(r, 7).Value = Application.Sum(Range(Cells(r, 7), Cells(r + n - 1, 7))) 'sum up the quantity column and the row below it.
    lr = Cells(Rows.Count, 1).End(xlUp).Row 'resets the counter
  End If
Next r
Application.ScreenUpdating = True
End Sub

**xladept** · 09-11-2012, 01:32 PM

Hi dirock,

Maybe:

n = WorksheetFunction.CountIf(Columns(12), Cells(r, 12).Value)
  If n > 1 Then
    Cells(r, 7).Value = WorksheetFunction.Sum(Range(Cells(r, 7), Cells(r + n - 1, 7))) 'sum up the quantity column and the row below it.

**dirock** · 09-12-2012, 08:33 AM

Unfortunately this code doesn't work. The team column doesn't get updated when it sums the quantity per team. Ok if you look at the allocation tab you will see what I need the code to cut and paste. So each team has a sum total amount that needs to be cut and pasted over the to allcoation tab along with pos_ind,underlying_Cusip, sum of quantiy,team, Settleday = today(), and Reason code will always be VO. Under the Amount cell I need to paste in the Account and quantity per acct. See the allocation tab before the end result.

I know this is a lot and I can hopefully figure out most of it but I just need a start with the looping and cut and paste process. Please help...

**xladept** · 09-12-2012, 02:31 PM

Hi Dirock,

This code will do what your code suggests:

Sub Option_Event_Writer()

Dim r As Long, lr As Long, n As Long
'Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
For r = 5 To lr
  n = Application.CountIf(Columns(12), Cells(r, 12).Value)
  If n > 1 Then
           'sum up the quantity column and the rows below it.
    Cells(r, 7).Value = Application.Sum(Range(Cells(r, 7), Cells(r + n - 1, 7)))
    
                        'resets the index
    r = r + n - 1
  End If
Next r
'Application.ScreenUpdating = True
End Sub