count in-between values

[vinu]

Hi,

I want to count the cells in a column that was in-between the
particular values..

i.e. I want the count of cells between cells that contains "Name".

In each sheet the "Name" will come min 10 time to max 30.

Pls Help.

Thanks in advance

Vinod

 

[Don Guillett]

Try this. Change sheet name & mc=4 to suit your column number
'=========
Option Explicit
Public myrow
Sub findnexttext()
Dim myrow As Long
Dim c As Range
Dim mc As Long
Dim firstaddress ' As Range
myrow = 0
mc = 4 'col D
With Worksheets(3).Columns(mc)
Set c = .Find(What:="Name", After:=Cells(1, mc), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

If Not c Is Nothing Then
firstaddress = c.Address
Do
' MsgBox c.Row
MsgBox c.Row - myrow
myrow = c.Row
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstaddress
End If
End With

End Sub