Function myCounts(rng) As String
ResultStr = ""
rngVals = rng.Value
For c = 2 To UBound(rngVals, 2)
Select Case True
Case UCase(Application.Trim(rngVals(1, c - 1))) = "OFF" And UCase(Application.Trim(rngVals(1, c))) <> "OFF": Start = c
Case UCase(Application.Trim(rngVals(1, c - 1))) <> "OFF" And UCase(Application.Trim(rngVals(1, c))) = "OFF"
If Start > 0 Then If Len(ResultStr) = 0 Then ResultStr = c - Start Else ResultStr = ResultStr & "-" & c - Start
End Select
Next c
myCounts = ResultStr
End Function
Thank you for your help and support.fareedexcel
Expected result attached for your reference.
Just modify those '-' and 'OFF' as You need...
The Code is working fine. Is there any way to understand the logic. I would like to learn more on automating stuffs in excel.See attached with UDF:
Code:Function myCounts(rng) As String ResultStr = "" rngVals = rng.Value For c = 2 To UBound(rngVals, 2) Select Case True Case UCase(Application.Trim(rngVals(1, c - 1))) = "OFF" And UCase(Application.Trim(rngVals(1, c))) <> "OFF": Start = c Case UCase(Application.Trim(rngVals(1, c - 1))) <> "OFF" And UCase(Application.Trim(rngVals(1, c))) = "OFF" If Start > 0 Then If Len(ResultStr) = 0 Then ResultStr = c - Start Else ResultStr = ResultStr & "-" & c - Start End Select Next c myCounts = ResultStr End Function
Thanks for your support Expert. I need one more modification from the same file, for eg: in the pattern if any of them has more than value 7 (6-8-6, 12-6-8,6-6-6-8) then those rows should come in another sheet. Is it possible with Macros instead of doing with formula.I'm sorry, but no; explaining the logic in full takes 10 to 20 or even 30 times as long as writing the code.
Sub blah()
Limit = 7
For Each rw In Sheets("Check").Range("I4:AM19").Rows
x = myCounts(rw)
If Len(x) > 0 Then
y = Split(x, "-")
GreaterThanLimit = False
For Each Z In y
If CLng(Z) > Limit Then
GreaterThanLimit = True
Exit For
End If
Next Z
If GreaterThanLimit Then Set Destn = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1) Else Set Destn = rw.Cells(1).Offset(, 33)
With Destn
.NumberFormat = "@"
.Value = x
End With
End If
Next rw
End Sub
After the pattern is captured in the 1st sheet. Above 7 should get captured in the second sheet. Please help on the same. Expected result attached. Thanks for the help Experts.Button at cell AP1 of Check sheet in attached, which calls the code below.
Uses the same user defined function as before.
Code:Sub blah() Limit = 7 For Each rw In Sheets("Check").Range("I4:AM19").Rows x = myCounts(rw) If Len(x) > 0 Then y = Split(x, "-") GreaterThanLimit = False For Each Z In y If CLng(Z) > Limit Then GreaterThanLimit = True Exit For End If Next Z If GreaterThanLimit Then Set Destn = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1) Else Set Destn = rw.Cells(1).Offset(, 33) With Destn .NumberFormat = "@" .Value = x End With End If Next rw End Sub
Output I have mentioned right nowfareedexcel
Yes ...
Do You have any name of that other sheet and to which row?
Do You have sample output?
This is Brilliant. Thanks for the SupportSee attached.
Thanks ExpertSee attached.