Formula or VBA Code
Posted by Jack on November 03, 2001 7:49 AM
Mon Tue Wed Thu Fri Sat Sun
1 0 0 0 1 1 0
0 0 0 1 0 1 1
0 1 0 1 0 0 0
1 0 0 0 0 1 1
What I would like is Formula or VBA to giv first 3 times 0 appears in a row consectivley
I need to return Mon Tues or Wed etc...as the answer which will really be a date - i have 200 Colums to read and thousands of rows.
If 4 in row fine or if two time 3 appear I need the first time.
I have thousands of these to do and by hand its hard and I make loads of mistakes.
I guess im hoping some VBA programmer might enjoy this. Pleas ehelp if you can
My big thanks in advance.
Posted by Damon Ostrander on November 03, 2001 10:41 AM
Posted by Jack on November 03, 2001 10:48 AM
Damon Last post was blank???
Sorry your last post was blank???
Posted by Damon Ostrander on November 03, 2001 10:54 AM
Sorry Jack--I got interrupted right in the middle of posting my reply.
Anyway, here is a formula that does what you described:
Function FirstZero(rngIn As Range, HeadIn As Range) As String
' Returns the cell value in HeadIn corresponding to the cell in
' rgnIn that is the start of 3 sequential zeros.
Dim iCol As Integer
For iCol = 1 To rngIn.Cells.Count - 3
If rngIn.Cells(iCol) = 0 Then
If rngIn.Cells(iCol + 1) = 0 Then
If rngIn.Cells(iCol + 2) = 0 Then
FirstZero = HeadIn.Cells(iCol).Value
Exit Function
End If
End If
End If
Next iCol
End Function
The function does not return anything if there are no instances of 3 consecutive zeros. When it finds 3 zeros, it returns the column heading for the column containing the first of the three zeros. If your sample data above starts in A1, then the function usage for the first row would be:
=FirstZero(A2:G2,A$1:G$1)
Happy computing.
Damon
Posted by Jack on November 03, 2001 11:19 AM
Hey no darmas, a very big thanks to you for that, i need to play and ill post a reply poss tommorow im not big on custom function so i look forward to the play. more starigth function and Button VBA (im ot very good thou-) , im in UK and now 8.30 i have 3 boys under 6 so bed time -- thanks again Damon
Cheers again! For iCol = 1 To rngIn.Cells.Count - 3 If rngIn.Cells(iCol) = 0 Then If rngIn.Cells(iCol + 1) = 0 Then If rngIn.Cells(iCol + 2) = 0 Then FirstZero = HeadIn.Cells(iCol).Value Exit Function End If End If End If Next iCol
