First occurance of text in a column

J

John

Column A has the text "Totals" in several random cells. In VBA I would like
2 variables that return the row numbers of the first occurances of "Totals"
in Column A of the rows above and below the active cell, regardless of where
the active cell is. Thank you.
 
M

Mike H

Hi,

This returns 2 variables. Highrow - the row above the active cell and LowRow.

Right click your sheet tab, view code and paste the code below in. I didn't
trap for not find the text and you may want to do this

Sub High_Low()
Dim HighRow As Long, LowRow As Long
Dim LastRow As Long
For x = ActiveCell.Row To 1 Step -1
If UCase(Cells(x, 1).Value) = "TOTALS" Then
HighRow = Rows(x).Row
Exit For
End If
Next

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For x = ActiveCell.Row To LastRow
If UCase(Cells(x, 1).Value) = "TOTALS" Then
LowRow = Rows(x).Row
Exit For
End If
Next
End Sub

Mike
 
G

Gary''s Student

Sub totals()
rrow = ActiveCell.Row
s = "Totals"
mesage = ""
For i = rrow To 1 Step -1
If Cells(i, "A").Value = s Then
mesage = Cells(i, "A").Row
Exit For
End If
Next

For i = rrow To Rows.Count
If Cells(i, "A").Value = s Then
mesage = mesage & Chr(10) & Cells(i, "A").Row
Exit For
End If
Next
MsgBox (mesage)
End Sub
 
L

Lars-Åke Aspelin

Column A has the text "Totals" in several random cells. In VBA I would like
2 variables that return the row numbers of the first occurances of "Totals"
in Column A of the rows above and below the active cell, regardless of where
the active cell is. Thank you.
Click to expand...


If you have "Totals" in cells A10, A20, A30, and A40, and the active
cell is cell A25 what do you want to have as the result?

A20 and A30 ?
or maybe
A10 and A30 ?

And if the active cell is A30 what do you want to have as the result?

Lars-Åke
 
Top