# Average interval between dates with random blanks

I have a spreadsheet with order date data: I need to find the average interval in days between each order date. I have to both find a way to get past the blank cells in the row, and also take into account that some clients have 5-10 orders and some clients have 2 orders when calculating my average frequency (interval) between orders.

Sub DateInt()
Dim CurrentSheet As Worksheet
Dim LastRow As Integer
Dim LastCol As Integer
Dim CurrentRow As Integer
Dim CurrentCol As Integer
Dim GrandT As String
Dim DateA As Date
Dim DateB As Date
Dim DateTtl As Integer
Dim DateCount As Integer

Set CurrentSheet = ActiveWorkbook.ActiveSheet
LastRow = CurrentSheet.Range("A" & Rows.Count).End(xlUp).Row - 1
LastCol = CurrentSheet.Cells(4, Columns.Count).End(xlToLeft).Column

Cells(4, LastCol + 1).Value = "Avg Interval"
Cells(4, LastCol + 2).Value = "Days Since Last Order"
Cells(4, LastCol + 3).Value = "Last Order Date"
Cells(4, LastCol + 4).Value = "Last Order v Avg Order"

For CurrentRow = 5 To LastRow
Cells(CurrentRow, LastCol).Value = Date
Cells(CurrentRow, LastCol).NumberFormat = "mm/dd/yy"
DateCount = 0
DateTtl = 0

For CurrentCol = 2 To LastCol
If Cells(CurrentRow, CurrentCol).Value = "" Then
Else
If DateCount < 1 Then
DateA = Cells(CurrentRow, CurrentCol).Value
Else
DateB = Cells(CurrentRow, CurrentCol).Value
DateTtl = DateDiff("d", DateA, DateB) + DateTtl

If DateValue(DateB) = DateValue(Date) Then
Else
DateA = DateB
End If
End If

DateCount = DateCount + 1
End If
Next CurrentCol

Cells(CurrentRow, LastCol + 1).Value = DateTtl / DateCount
Cells(CurrentRow, LastCol + 1).NumberFormat = "General"
Cells(CurrentRow, LastCol + 2).Value = DateDiff("d", DateA, Date)
Cells(CurrentRow, LastCol + 2).NumberFormat = "General"
Cells(CurrentRow, LastCol + 3).Value = DateA
Cells(CurrentRow, LastCol + 3).NumberFormat = "mm/dd/yy"
Cells(CurrentRow, LastCol + 4).Value = Cells(CurrentRow, LastCol + 1).Value - Cells(CurrentRow, LastCol + 2).Value
Cells(CurrentRow, LastCol + 4).NumberFormat = "#,##0_);[Red](#,##0)"
Next CurrentRow

End Sub


Here is what my code ended up doing: One thing that stands right out is formatting cells in each iteration of a loop. Less to do within each iteration should speed things up just a bit so why not just format the entire column after the loop has done all the calculations?

I am not sure sure if you are working with more than one workbook or if that's part of an automated process but

Set CurrentSheet = ActiveWorkbook.ActiveSheet
LastRow = CurrentSheet.Range("A" & Rows.Count).End(xlUp).Row - 1
LastCol = CurrentSheet.Cells(4, Columns.Count).End(xlToLeft).Column


seems like a bit of an overkill. You are not using the CurrentSheet (pascal case vs camel case you know) anywhere in the code but you don't quality the Cells. Why not just:

lastRow = Range("A" & Rows.Count).End(xlUp).Row - 1
lastCol = Cells(4, Columns.Count).End(xlToLeft).Column


Have you heard of offsets? Experience taught me that it's easier to maintain offsets rather than +1s +2s etc.

Cells(CurrentRow, LastCol + 1).Value = DateTtl / DateCount


=

Cells(currentRow, lastCol).Offset(0,1) = DateTtl / DateCount