Excel VBA: Return first occurrence of a word in a column? The last?

Question

Say I have, in an arbitrary column, these values from row 1 down:

1 A
2 A
3 A
4 A
5 B
6 B
7 B
8 A
9 A
10 A

I want to be able to say start=5 is the first B and last=7 is the last B. If there are no B's return 0 for both first and last.

Answer 1

Don't forget that in VBA you still have access to a wealth of built-in Excel functions. Examples (assuming your data is in column 1):

Find the first B...
Columns(1).Find(What:="B", LookAt:=xlWhole, MatchCase:=False).Row 'Returns 5

Find the last B...
Columns(1).Find(What:="B", LookAt:=xlWhole, SearchDirection:=xlPrevious, MatchCase:=False).Row 'Returns 7

If B is not found, then an error is returned. We can take advantage of this by using error handling to return 0 if B is not found. Putting it all together...

Sub DoTest()
  Dim RowFirst As Integer, _
      RowLast As Integer
  On Error GoTo ErrorHandler
  RowFirst = Columns(1).Find(What:="B", LookAt:=xlWhole, SearchDirection:=xlNext, MatchCase:=False).Row
  RowLast = Columns(1).Find(What:="B", LookAt:=xlWhole, SearchDirection:=xlPrevious, MatchCase:=False).Row
  Exit Sub
ErrorHandler:
  RowFirst = 0
  RowLast = 0
End Sub