Loop using Month() and Year()
 

I have a list of jobs with a report date (column V), part delivery date
(column X) and job start date (column Y). I'm trying to write a macro
that will output the average queue time (start date - part delivery
date) for each report month. This macro must only operate on the jobs
that have been "closed", according the the value in column B. The data
starts at row 5 and finishes at row 26. Ideally, I'd like columns AD,
AE and AF to contain 36 entries for year, month and average queue
time.

This is what I have so far, but the average does not reset when the
month changes, and it won't output each year 12 times to correspond
with the month. Any help would be greratly appreciated.

ash


Sub Report_Queue()

Dim Report, ReportDate, ReportMonth, ReportYear
Dim dbaseRow
Dim QueueMonth, QueueYear
Dim QueueDays, QueueCount, AvgQueueDays

QueueCount = 0
dbaseRow = 5


Do
ReportDate = Sheets("database").Range("v" & dbaseRow).Value
ReportMonth = Month(ReportDate)
ReportYear = Year(ReportDate)

If Sheets("database").Range("b" & dbaseRow).Value = "Closed" Then

For QueueYear = 2005 To 2007
For QueueMonth = 1 To 12
If ReportMonth = QueueMonth Then
QueueCount = QueueCount + 1
QueueDays = QueueDays + Sheets("database").Range("Y" &
dbaseRow).Value _
- Sheets("database").Range("X" & dbaseRow).Value
AvgQueueDays = QueueDays / QueueCount

Sheets("database").Range("ad" & ((QueueYear - 2004) +
5)).Value = QueueYear
Sheets("database").Range("ae" & (QueueMonth + 5)).Value
= QueueMonth
Sheets("database").Range("af" & (QueueMonth + 5)).Value
= AvgQueueDays

End If

Next QueueMonth

Next QueueYear
End If
dbaseRow = dbaseRow + 1
Loop Until dbaseRow = 26

End Sub


--
AMansell
------------------------------------------------------------------------
AMansell's Profile: http://www.excelforum.com/member.php...o&userid=32205
View this thread: http://www.excelforum.com/showthread...hreadid=519567