Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Do Loop or use End iF for search string
50 colums (BI:DC) 1500 rows (500 to 2000)
On the cs772nd row, cs797th row, cs822nd row and so on, in each column I have the word "stop". The data is already stored or I am storing data into these colums: Depending on where I am storing data, I would like to search that column for the word stop, and I want my active cell to be just above the word stop. SO If I am in CU775 (Activecell) than I would like to search that column CU for the word stop down to row 797 and my active cell would be CU796. What would be the best way for me to approach this with not too much coding as I need to accomodate this to another marco already in place. Thanks, Rick |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Do Loop or use End iF for search string
Try this:
Cells(Columns(ActiveCell.Column).Find(What:="stop" , _ After:=ActiveCell, LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Row - 1, ActiveCell.Column).Select You may have to apply On Error for handling cases when "stop" is not found! Regards, Stefi €˛RGreen€¯ ezt Ć*rta: 50 colums (BI:DC) 1500 rows (500 to 2000) On the cs772nd row, cs797th row, cs822nd row and so on, in each column I have the word "stop". The data is already stored or I am storing data into these colums: Depending on where I am storing data, I would like to search that column for the word stop, and I want my active cell to be just above the word stop. SO If I am in CU775 (Activecell) than I would like to search that column CU for the word stop down to row 797 and my active cell would be CU796. What would be the best way for me to approach this with not too much coding as I need to accomodate this to another marco already in place. Thanks, Rick |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Do Loop or use End iF for search string
Try the below macro
Sub Macro() Dim varFound As Variant Set varFound = Columns(ActiveCell.Column).Find("Stop") If Not varfound Is Nothing Then varfound.Offset(-1, 0).Activate End Sub If this post helps click Yes --------------- Jacob Skaria "RGreen" wrote: 50 colums (BI:DC) 1500 rows (500 to 2000) On the cs772nd row, cs797th row, cs822nd row and so on, in each column I have the word "stop". The data is already stored or I am storing data into these colums: Depending on where I am storing data, I would like to search that column for the word stop, and I want my active cell to be just above the word stop. SO If I am in CU775 (Activecell) than I would like to search that column CU for the word stop down to row 797 and my active cell would be CU796. What would be the best way for me to approach this with not too much coding as I need to accomodate this to another marco already in place. Thanks, Rick |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Do Loop or use End iF for search string
Oops...correction..
Sub Macro() Dim varFound As Variant Set varFound = Range(ActiveCell, Cells(Rows.Count, _ ActiveCell.Column).End(xlUp)).Find("Stop") If Not varFound Is Nothing Then varFound.Offset(-1, 0).Activate End Sub If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Try the below macro Sub Macro() Dim varFound As Variant Set varFound = Columns(ActiveCell.Column).Find("Stop") If Not varfound Is Nothing Then varfound.Offset(-1, 0).Activate End Sub If this post helps click Yes --------------- Jacob Skaria "RGreen" wrote: 50 colums (BI:DC) 1500 rows (500 to 2000) On the cs772nd row, cs797th row, cs822nd row and so on, in each column I have the word "stop". The data is already stored or I am storing data into these colums: Depending on where I am storing data, I would like to search that column for the word stop, and I want my active cell to be just above the word stop. SO If I am in CU775 (Activecell) than I would like to search that column CU for the word stop down to row 797 and my active cell would be CU796. What would be the best way for me to approach this with not too much coding as I need to accomodate this to another marco already in place. Thanks, Rick |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Do Loop or use End iF for search string
Hey Jacob, this did the trick
Stefi, haven't tried it yet, but thanks for the posting. Rick. "Jacob Skaria" wrote: Try the below macro Sub Macro() Dim varFound As Variant Set varFound = Columns(ActiveCell.Column).Find("Stop") If Not varfound Is Nothing Then varfound.Offset(-1, 0).Activate End Sub If this post helps click Yes --------------- Jacob Skaria "RGreen" wrote: 50 colums (BI:DC) 1500 rows (500 to 2000) On the cs772nd row, cs797th row, cs822nd row and so on, in each column I have the word "stop". The data is already stored or I am storing data into these colums: Depending on where I am storing data, I would like to search that column for the word stop, and I want my active cell to be just above the word stop. SO If I am in CU775 (Activecell) than I would like to search that column CU for the word stop down to row 797 and my active cell would be CU796. What would be the best way for me to approach this with not too much coding as I need to accomodate this to another marco already in place. Thanks, Rick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
search string for number value | Excel Discussion (Misc queries) | |||
How to search a string from the right ? | Setting up and Configuration of Excel | |||
to search for a string and affect data if it finds the string? | Excel Worksheet Functions | |||
SUMPRODUCT and search string | Excel Worksheet Functions | |||
Q: search in string | Excel Discussion (Misc queries) |