Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
First, Last occurance of date in column | Excel Worksheet Functions | |||
Find Every Occurance matching one Row and One Column | Excel Discussion (Misc queries) | |||
Countinge the occurance of numbers in a column | New Users to Excel | |||
How do I select the first occurance of a number in a column | Excel Programming | |||
Can I divide a column at the first "space" occurance | Excel Worksheet Functions |