Finding records that have 12 consecutive values.

  • Thread starter Thread starter Captain Snuggles
  • Start date Start date
C

Captain Snuggles

I'm trying to find all the people in my spreadsheet who have given
donations for 12 consecutive months in the last 60 months. I know how
to do a simple count of all donations in the last 60 months, but I'm
not sure how to write a formula that will identify those who gave
consecutively for any 12 month period in th last 60 months. Any
ideas?
This is how I have my spreadsheet formatted.

Name | Month1| Month2| Month3| Month4| | Month5| | Month6| |
Month7| | Month8| ...
Johan Doe| $10 | $5 | $0 | $100 | $100 |
$0 | $10 | $10 |
 
The following will make green any name who has contributed 12
consecutive months. Change rows and sheet name to suit.

Sub Macro1()
Dim iCol As Integer
Dim iCt As Integer
Dim iRow As Integer

For iRow = 2 To 6
iCt = 0
For iCol = 2 To 61
If Sheets("Sheet1").Cells(iRow, iCol) > 0 Then
iCt = iCt + 1
Else
iCt = 0
End If
If iCt = 12 Then
Sheets("Sheet1").Cells(iRow, 1).Interior.ColorIndex = 4
Exit For
End If
Next iCol
Next iRow
End Sub

Hth,
Merjet
 
Back
Top