count 1st cell and last cell that contain specific word

[crapit]

From a column e.g "A"
How to determine the 1st cell and the last cell that contain a specific
word?

A B C
1
2 grp
3 grp
4
5 grp
6
7 hex
8 hex
9 hex
10 grp
11 grp

 

[paul.robinson]

Hi
Assuming Your column A data has a heading

Sub tester()
Dim firstCell As Range, lastCell As Range
With Range("A:A")
Set firstCell = .Find("grp", SearchDirection:=xlNext)
Set lastCell = .Find("grp", SearchDirection:=xlPrevious)
End With
MsgBox "First Cell address is " & firstCell.Address
MsgBox "Last Cell address is " & lastCell.Address
End Sub

The first cell tested is A2 for firstcell and last row of data for
lastcell.
Check the find method Help if you want to specify different start
cells for searching.
regards
Paul

 

[Gary''s Student]

Select column A and run:

Sub find_um()
firstt = False
v = "grp"
For Each r In Selection
If v = r.Value Then
If firstt Then
lastone = r.Address
Else
lastone = r.Address
firstone = r.Address
firstt = True
End If
End If
Next
MsgBox (firstone & Chr(10) & lastone)
End Sub

 

[crapit]

err, possible not to use macro? formula

Hi
Assuming Your column A data has a heading

Sub tester()
Dim firstCell As Range, lastCell As Range
With Range("A:A")
Set firstCell = .Find("grp", SearchDirection:=xlNext)
Set lastCell = .Find("grp", SearchDirection:=xlPrevious)
End With
MsgBox "First Cell address is " & firstCell.Address
MsgBox "Last Cell address is " & lastCell.Address
End Sub

The first cell tested is A2 for firstcell and last row of data for
lastcell.
Check the find method Help if you want to specify different start
cells for searching.
regards
Paul

 

[Don Guillett]

Array enter
=MIN(IF(A2:A21="a",ROW(A2:A21)))
=Max(IF(A2:A21="a",ROW(A2:A21)))

 

[paul.robinson]

Hi
Post the question in

microsoft.public.excel.worksheetfunctions

to avoid disappointment!
Paul

 

[crapit]

do i need to put the array across the same row

Array enter
=MIN(IF(A2:A21="a",ROW(A2:A21)))
=Max(IF(A2:A21="a",ROW(A2:A21)))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[Don Guillett]

This is an Array formula that could be put anywhere on the worksheet. It
refers to the indicated range.
Instead of entering/editing with just ENTER, hold down the ctrl & shift keys
and touch enter at the same time.

 

[crapit]

If the reference is in another worksheet, can I just change to =MIN(IF("abc
num"(A2:A21)="a",ROW("abc num"(A2:A21))))

 

[crapit]

Pls ignore the worksheet Qs. If I need to use the min and max in a range
e.g sum('abc ge'!a3:a33), where a3 is the value in MIN array and a33 in MAX
array,is that possible

 

[Don Guillett]

More info. Your OP asked to determine TEXT in a column. In the future,
ALWAYS state your requirement in the FIRST post so time is not wasted. I
don't understand what you want.