FIND FIRST AND LAST

[Sunil Patel]

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

 

[Jim Cone]

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



"Sunil Patel" <[email protected]>
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

 

[Dave Peterson]

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).

 

[Rick Rothstein]

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
NotThere:
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 Rothstein]

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.

 

[muddan madhu]

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))