First occurance of text in a column
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. |
First occurance of text in a column
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 "John" wrote: 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. |
First occurance of text in a column
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 -- Gary''s Student - gsnu2007L "John" wrote: 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. |
First occurance of text in a column
On Sun, 8 Feb 2009 12:43:46 -0800, "John" wrote:
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. 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 |
All times are GMT +1. The time now is 04:21 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com