Count Cells

[helpexcel]

Hi - If i have Data in column A, that also has multiple headings, is there a way for me to say in A:A count cells between "Beach" and "Baseball"?

 

[jolivanes]

One way would be

Sub Maybe()
    MsgBox Range(Columns(1).Find("Beach").Offset(1), Columns(1).Find("Baseball").Offset(-1)).Cells.Count
End Sub

 

[helpexcel]

that works, thanks!

do you know if there's a way to use a formula in the spreadsheet?

 

[Marcelo Branco]

What is the expected result for the scenario below?


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Baseball​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

Beach​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td][/td][/tr]
[/table]

M.

 

[jolivanes]

=MATCH("Baseball", A2:A29, 0)-MATCH("Beach", A2:A29, 0)-1

If you don't know which value is going to be the first one, you might have to resort t something like this.

Sub Maybe_C()
Dim i As Long, ii As Long
i = ActiveSheet.Columns(1).Find("Beach").Row
ii = ActiveSheet.Columns(1).Find("Baseball").Row
    MsgBox IIf(ii > i, ii - (i + 1), i - (ii + 1))
End Sub

@Marcelo
We'll wait for an answer I guess but the way I read the first Post, it should be 1 (one)

 

[helpexcel]

Yes @jolivanes i would expect 1.

 

[Marcelo Branco]

Try

=ABS(MATCH("Beach",A:A,0)-MATCH("Baseball",A:A,0))-1

M.