Count between Two Cells

[fareedexcel]

Dear Experts,

I require to check the count interval between the 1st OFF to the Last OFF. After the Last OFF, count should not happen. Expected result attached for your reference.

 

[vletm]

fareedexcel
Expected result attached for your reference.
Just modify those '-' and 'OFF' as You need...

 

[p45cal]

See attached with UDF:

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

 

[fareedexcel]

fareedexcel
Expected result attached for your reference.
Just modify those '-' and 'OFF' as You need...

Thank you for your help and support.

 

[fareedexcel]

See attached with UDF:

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

The Code is working fine. Is there any way to understand the logic. I would like to learn more on automating stuffs in excel.

 

[p45cal]

I'm sorry, but no; explaining the logic in full takes 10 to 20 or even 30 times as long as writing the code.

 

[fareedexcel]

I'm sorry, but no; explaining the logic in full takes 10 to 20 or even 30 times as long as writing the code.

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.

 

[vletm]

fareedexcel
Yes ...
Do You have any name of that other sheet and to which row?
Do You have sample output?

 

[p45cal]

Button at cell AP1 of Check sheet in attached, which calls the code below.
Uses the same user defined function as before.

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

 

[fareedexcel]

Button at cell AP1 of Check sheet in attached, which calls the code below.
Uses the same user defined function as before.

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.

 

[fareedexcel]

fareedexcel
Yes ...
Do You have any name of that other sheet and to which row?
Do You have sample output?

Output I have mentioned right now

 

[vletm]

fareedexcel
Output as You have mentioned right now...

 

[p45cal]

See attached.

 

[fareedexcel]

See attached.

This is Brilliant. Thanks for the Support

 

[fareedexcel]

See attached.

Thanks Expert