PDA

View Full Version : Read Column B for dates and calculate averages



austenr
10-12-2005, 01:09 PM
I have another problem. I have a sheet with dates in them formatted as 01/01/2005, 02/01/2005, etc. What I need to do is read the row until a date is encountered and add 1 to a counter, then check the next row to see if it contains a date, add 1, etc. continue reading until there is a cell that does not contain a date. When that happens select a range of cells correcspoding to the dates and divide the total of those cells by the counter total to get the average. Continue reading column B. I have attached a spreadsheet to illustrate. Thanks in advance for the help.

BlueCactus
10-14-2005, 10:05 PM
Here's one idea for you:
Dim lastRow As Long, i As Long, firstLine As Long, addData As Variant, addTotal As Double
Dim j As Long, k As Integer

lastRow = ActiveSheet.Range("B65536").End(xlUp).Row
firstLine = 0

For i = 1 To lastRow
If firstLine = 0 And IsDate(ActiveSheet.Cells(i, 2)) Then firstLine = i
If firstLine And (Not IsDate(ActiveSheet.Cells(i, 2)) Or i = lastRow) Then
If i = lastRow Then i = i + 1
addData = ActiveSheet.Cells(firstLine, 5).Resize(i - firstLine, 4)
addTotal = 0
For j = 1 To UBound(addData, 1)
For k = 1 To UBound(addData, 2)
addTotal = addTotal + addData(j, k)
Next k
Next j
ActiveSheet.Cells(i - 1, 11) = addTotal / UBound(addData, 1)
firstLine = 0
End If
Next i