• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Count between Two Cells

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
 

Attachments

p45cal said:
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
Click to expand...
The Code is working fine. Is there any way to understand the logic. I would like to learn more on automating stuffs in excel.
 
I'm sorry, but no; explaining the logic in full takes 10 to 20 or even 30 times as long as writing the code.
 
p45cal said:
I'm sorry, but no; explaining the logic in full takes 10 to 20 or even 30 times as long as writing the code.
Click to expand...
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.
 
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
 

Attachments

Last edited:
p45cal said:
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
Click to expand...
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.
 

Attachments

You must log in or register to reply here.
Back
Top