Thread
:
Finding the longest contiguous 1-d array of 0's
View Single Post
#
3
Posted to microsoft.public.excel.misc
Don Guillett
external usenet poster
Posts: 10,124
Finding the longest contiguous 1-d array of 0's
Try this where text in col a and numbers in col 2-11
Sub findlongestzeros()
For i = 1 To Cells(Rows.Count, "a").End(xlUp).Row
mc = 0
For j = 2 To 11
If Cells(i, j) = 0 And Cells(i, j + 1) = 0 Then mc = mc + 1
Next j
'MsgBox mc
If mc mss Then
mss = mc
mr = i
End If
Next i
MsgBox "Max is Row " & mr
'MsgBox mss
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"MJW" wrote in message
...
Hi All,
Ok, I can't think of any remotely-easy way to do this. My data exists as
such: accounts listed in the rows, dates (by month) listed in the
columns;
in the fields corresponding to each account/dates is the quantity of items
the account purchased in that month.
Is it formulaically possible (barring VBA) to have Excel calculate the
longest contiguous string of zero-value cells for each row? (What I'm
trying to discern is a historical gap-analysis for the longest no-purchase
period of a given row/account.) Any thoughts or suggestions are greatly
appreciated, as always.
Thanks,
Jamie W.
Reply With Quote
Don Guillett
View Public Profile
Find all posts by Don Guillett