Average interval between dates with random blanks

Question

I have a spreadsheet with order date data:

enter image description here

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
 DateC = DateAdd("d", 20, Date)
 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:

enter image description here

Answer 1

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