FIND FIRST AND LAST
 

hi there,

When searching column "a", how can i return the row numbers of the first and
last cell in which there is a particular word

dog
cat
cat
cat
fish

i want to return firstrow%=2 and lastrow%=4 if looking for "cat"

Using excel 2000

thanks

FIND FIRST AND LAST
 

See the example in Excel VBA help under "Find"
--
Jim Cone
Portland, Oregon USA



"Sunil Patel"
wrote in message
hi there,
When searching column "a", how can i return the row numbers of the first and
last cell in which there is a particular word

dog
cat
cat
cat
fish

i want to return firstrow%=2 and lastrow%=4 if looking for "cat"
Using excel 2000
thanks

FIND FIRST AND LAST
 

Option Explicit
Sub testme()
Dim FirstRow As Long
Dim LastRow As Long
Dim FoundCell As Range
Dim WhatToFind As String

WhatToFind = "Cat"

With Worksheets("Sheet1")
With .Range("A:A")
Set FoundCell = .Cells.Find(What:=WhatToFind, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
MsgBox WhatToFind & " wasn't found!"
Else
FirstRow = FoundCell.Row

Set FoundCell = .Cells.Find(What:=WhatToFind, _
After:=.Cells(1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)

If FoundCell Is Nothing Then
'this shouldn't happen since it was already found
Else
LastRow = FoundCell.Row
End If

If FirstRow = LastRow Then
MsgBox "Only one found in row: " & FirstRow
Else
MsgBox "Found in rows: " & FirstRow & " " & LastRow
End If

End If
End With
End With

End Sub

I looked for a match in the whole cell (will not Catatonic or concatenate).


Sunil Patel wrote:

hi there,

When searching column "a", how can i return the row numbers of the first and
last cell in which there is a particular word

dog
cat
cat
cat
fish

i want to return firstrow%=2 and lastrow%=4 if looking for "cat"

Using excel 2000

thanks

--

Dave Peterson

FIND FIRST AND LAST
 

Without the benefit of seeing your existing code, the best I can do is give
you a framework to follow...

' <<Your "lead-in" code goes here
'
Word = "cat"
On Error GoTo NotThere
FirstRow = Range("A:A").Find(Word, LastCell, , , , xlNext).Row
LastRow = Range("A:A").Find(Word, LastCell, , , , xlPrevious).Row
NotThe
On Error GoTo 0
'
' <<Rest of your code goes here

If the word is not in the column, then FirstRow and LastRow will be set to
zero.

--
Rick (MVP - Excel)


"Sunil Patel" wrote in message
...
hi there,

When searching column "a", how can i return the row numbers of the first
and last cell in which there is a particular word

dog
cat
cat
cat
fish

i want to return firstrow%=2 and lastrow%=4 if looking for "cat"

Using excel 2000

thanks

FIND FIRST AND LAST
 

If the word is not in the column, then FirstRow and LastRow will be set to
zero.

I need to qualify the above statement. If your code calculates FirstRow and
LastRow within a loop, then these variables will retain the value they had
during the previous iteration of the loop. To avoid that problem (again,
this is ONLY if these variables are calculated within a loop), add these two
statements...

FirstRow = 0
LastRow = 0

immediately after the On Error GoTo NotThere statement.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Without the benefit of seeing your existing code, the best I can do is
give you a framework to follow...

' <<Your "lead-in" code goes here
'
Word = "cat"
On Error GoTo NotThere
FirstRow = Range("A:A").Find(Word, LastCell, , , , xlNext).Row
LastRow = Range("A:A").Find(Word, LastCell, , , , xlPrevious).Row
NotThe
On Error GoTo 0
'
' <<Rest of your code goes here

If the word is not in the column, then FirstRow and LastRow will be set to
zero.

--
Rick (MVP - Excel)


"Sunil Patel" wrote in message
...
hi there,

When searching column "a", how can i return the row numbers of the first
and last cell in which there is a particular word

dog
cat
cat
cat
fish

i want to return firstrow%=2 and lastrow%=4 if looking for "cat"

Using excel 2000

thanks

FIND FIRST AND LAST
 

using formula

To know the first row =ROW(INDEX(A1:A20,MATCH("cat",A1:A20,0),0))

Use Ctrl + Shift + Enter
To know the last row =ROW(INDEX(A1:A20,SMALL(IF(A1:A20="cat",ROW
(A1:A20)),COUNTIF(A1:A20,"cat")),0))


On Apr 5, 5:21*am, "Sunil Patel" wrote:
hi there,

When searching column "a", how can i return the row numbers of the first and
last cell in which there is a particular word

dog
cat
cat
cat
fish

i want to return firstrow%=2 and lastrow%=4 if looking for "cat"

Using excel 2000

thanks