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?

	A​
1​
2​
3​
4​	Baseball​
5​
6​	Beach​
7​

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.