1. ### Welcome to Chandoo.org Forums. Short message for you

Hi Guest,

Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

Yours,
Chandoo
2. 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...

3. 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

Discussion in 'Ask an Excel Question' started by fareedexcel, Oct 11, 2017 at 5:25 PM.

1. ### fareedexcelNew Member

Messages:
12
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.

File size:
13.3 KB
Views:
12
2. ### vletmWell-Known Member

Messages:
2,800
fareedexcel
Expected result attached for your reference.
Just modify those '-' and 'OFF' as You need...

File size:
16.9 KB
Views:
7
3. ### p45calWell-Known Member

Messages:
822
See attached with UDF:
Code (vb):
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

#### Attached Files:

• ###### Chandoo36062check-count-between-two-cells-check-count - 1.xlsm
File size:
21.2 KB
Views:
4
Thomas Kuriakose likes this.
4. ### fareedexcelNew Member

Messages:
12
Thank you for your help and support.
5. ### fareedexcelNew Member

Messages:
12
The Code is working fine. Is there any way to understand the logic. I would like to learn more on automating stuffs in excel.
6. ### p45calWell-Known Member

Messages:
822
I'm sorry, but no; explaining the logic in full takes 10 to 20 or even 30 times as long as writing the code.
7. ### fareedexcelNew Member

Messages:
12
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.
8. ### vletmWell-Known Member

Messages:
2,800
fareedexcel
Yes ...
Do You have any name of that other sheet and to which row?
Do You have sample output?
9. ### p45calWell-Known Member

Messages:
822
Button at cell AP1 of Check sheet in attached, which calls the code below.
Uses the same user defined function as before.
Code (vb):
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

#### Attached Files:

• ###### Chandoo36062check-count-between-two-cells-check-count - 2.xlsm
File size:
27.2 KB
Views:
1
Last edited: Oct 13, 2017 at 10:08 AM
10. ### fareedexcelNew Member

Messages:
12
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.

File size:
21.3 KB
Views:
4
11. ### fareedexcelNew Member

Messages:
12
Output I have mentioned right now
12. ### vletmWell-Known Member

Messages:
2,800
fareedexcel
Output as You have mentioned right now...

#### Attached Files:

• ###### check-count-between-two-cells-check-count - 1.xlsb
File size:
21.2 KB
Views:
3
fareedexcel and Thomas Kuriakose like this.
13. ### p45calWell-Known Member

Messages:
822
See attached.

#### Attached Files:

• ###### Chandoo36062check-count-between-two-cells-check-count - 3.xlsm
File size:
26.4 KB
Views:
5
fareedexcel and Thomas Kuriakose like this.
14. ### fareedexcelNew Member

Messages:
12
This is Brilliant. Thanks for the Support
15. ### fareedexcelNew Member

Messages:
12
Thanks Expert