VBA calculate avg dates
 

In column A I have the Customer Number, Column B = Item Number, Column
J = Dates. I am trying to find a quick way to calculate the average
days between orders for the same item for the same customer. I was
trying to put it into a formula and then paste into teh right cells.
This takes a very long time.

Thanks,
Jay

Sub AvgThisItem()
Application.ScreenUpdating = False
Dim Rng As Range
Dim i
Dim Lstrow As Long
Dim Fn As String

Lstrow = Range("A6").End(xlDown).Row
Set Rng = Range("B6", Range("B6").End(xlDown))

Range("K5") = "Days"
Range("L5") = "Avg Days"
Range("N1") = "=sumproduct(($B$6:$b$" & Lstrow & "=$B1)*($A$6:$a$" & _
Lstrow & "=$A1)*(1))"
Range("O1") = "=sumproduct(($B$6:$b$" & Lstrow & "=$B1)*($A$6:$a$" & _
Lstrow & "=$A1)*($K$6:$k$" & Lstrow & "))"
Range("K1") = "=ABS(IF(B1=B2,J1-J2,0))"
Range("K1:O1").Copy
Range("K6:K" & Lstrow).PasteSpecial xlPasteFormulas
For Each i In Rng
If i < i.Offset(-1, 0) Then
Range("L" & i.Row).Value = "=O" & i.Row & "/n" & i.Row
Else
End If
Next i
End Sub

VBA calculate avg dates
 

Jay,
Arrange your data all by colums A, B and J (here in ascending order) first.
You will get another view right away.
--
Regards
Petr Bezucha


"jlclyde" wrote:

In column A I have the Customer Number, Column B = Item Number, Column
J = Dates. I am trying to find a quick way to calculate the average
days between orders for the same item for the same customer. I was
trying to put it into a formula and then paste into teh right cells.
This takes a very long time.

Thanks,
Jay

Sub AvgThisItem()
Application.ScreenUpdating = False
Dim Rng As Range
Dim i
Dim Lstrow As Long
Dim Fn As String

Lstrow = Range("A6").End(xlDown).Row
Set Rng = Range("B6", Range("B6").End(xlDown))

Range("K5") = "Days"
Range("L5") = "Avg Days"
Range("N1") = "=sumproduct(($B$6:$b$" & Lstrow & "=$B1)*($A$6:$a$" & _
Lstrow & "=$A1)*(1))"
Range("O1") = "=sumproduct(($B$6:$b$" & Lstrow & "=$B1)*($A$6:$a$" & _
Lstrow & "=$A1)*($K$6:$k$" & Lstrow & "))"
Range("K1") = "=ABS(IF(B1=B2,J1-J2,0))"
Range("K1:O1").Copy
Range("K6:K" & Lstrow).PasteSpecial xlPasteFormulas
For Each i In Rng
If i < i.Offset(-1, 0) Then
Range("L" & i.Row).Value = "=O" & i.Row & "/n" & i.Row
Else
End If
Next i
End Sub