I want to count rows between the occurence of one word.

[Guest]

I need to count rows between the occurence of one word - eg. "ticket".

 

[Chip Pearson]

Try something like the following:

Dim FirstFind As Range
Dim SecondFind As Range
Dim RowsBetween As Long
Set FirstFind = Range("A1:A100").Find(what:="ticket",
LookIn:=xlValues, _
lookat:=xlWhole, MatchCase:=False)
If Not FirstFind Is Nothing Then
Set SecondFind = Range("A1:A100").FindNext(after:=FirstFind)
If Not SecondFind Is Nothing Then
RowsBetween = SecondFind.Row - FirstFind.Row - 1
Debug.Print RowsBetween
End If
End If



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Bob Umlas]

if ticket is in column D:
=MATCH("ticket",OFFSET(D1,MATCH("Ticket",D,0),0,10000,1),0)-1
Bob Umlas