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

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

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

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

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

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