Results 1 to 3 of 3

Thread: Count Add And get average

  1. #1

    Count Add And get average



    Register for a FREE account, and/
    or Log in to avoid these ads!

    hi so i have this report that i have to do all the time manually i was wondering if anybody can help me modify this macro to be able to add and get an average
    the macro to make the count works perfectly i just don't know how to modify it to add and average
    thank you for all you help

    Code:
    Sub Count()
    'this will count how many times the client name apears
    For lRows = ActiveSheet.UsedRange.Rows.Count To 2 Step -1
    If Cells(lRows, 2).Value <> Cells(lRows - 1, 2).Value Then
    Cells(lRows, 7).Value = importCounter
    importCounter = 1
    Else
    'Cells(lRows, 1).EntireRow.Delete
    importCounter = importCounter + 1
    End If
    Next lRows
    End Sub
    Code:
    Sub add()
    'this one will add Principal received from client
    For lRows = ActiveSheet.UsedRange.Rows.Count To 2 Step -1
    If Cells(lRows, 5).Value <> Cells(lRows - 1, 5).Value Then
    Cells(lRows, 8).Value = importCounter
    importCounter = 1
    Else
    'Cells(lRows, 1).EntireRow.Delete
    importCounter = importCounter + 1
    End If
    Next lRows
    End Sub
    Code:
    Sub average ()
    'this one will get the average amount Principal received from client
    For lRows = ActiveSheet.UsedRange.Rows.Count To 2 Step -1
    If Cells(lRows, 5).Value <> Cells(lRows - 1, 5).Value Then
    Cells(lRows, 9).Value = importCounter
    importCounter = 1
    Else
    'Cells(lRows, 1).EntireRow.Delete
    importCounter = importCounter + 1
    End If
    Next lRows
    End Sub
    Attached Files Attached Files

  2. #2
    Code:
    Sum Aggregations()
    Const FORMULA_COUNT = "=IF($B2=$B1,"""",COUNTIF($B$2:$B<lastrow>,$B2))"
    Const FORMULA_SUM = "=IF($B2=$B1,"""",SUMIF($B$2:$B<lastrow>,$B2,$E$2:$E<lastrow>))"
    Const FORMULA_AVERAGE = "=IF($B2=$B1,"""",AVERAGEIF($B$2:$B<lastrow>,$B2,$E$2:$E<lastrow>))"
    Dim lRows As Long
    
    
        Application.ScreenUpdating = False
    
        'this will count how many times the client name apears
        With ActiveSheet
        
            lRows = .Cells(.Rows.Count, "B").End(xlUp).Row
            
            .Range("G2").Resize(lRows - 1, 3).NumberFormat = "General"
            
            .Range("G2").Resize(lRows - 1).Formula = Replace(FORMULA_COUNT, "<lastrow>", lRows)
            .Range("H2").Resize(lRows - 1).Formula = Replace(FORMULA_SUM, "<lastrow>", lRows)
            .Range("I2").Resize(lRows - 1).Formula = Replace(FORMULA_AVERAGE, "<lastrow>", lRows)
            
            With .Range("G2").Resize(lRows - 1, 3)
            
                .Value = .Value
                .NumberFormat = "#,##0"
            End With
        End With
    
        Application.ScreenUpdating = True
    End Sub
    Last edited by Bob Phillips; 2014-08-06 at 08:21 AM.

  3. #3
    THANK YOU VERY MUCH this macro works great :-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •