Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stop a find after the last occrance of the search value.
Is there any way to cause this routine to stop when it finds the last
occurance of NoXXX and not start over at the top of the worksheet? Sub NextRow() ' ' Macro4 Macro ' ' Cells.Find(What:="NoXXX", After:=ActiveCell, LookIn:=xlValues, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stop a find after the last occrance of the search value.
See the example for the Find method... it shows you exactly how to set up
the loop. To get to the help files for it, put the text cursor on the word Find and press F1. -- Rick (MVP - Excel) "Pats" wrote in message ... Is there any way to cause this routine to stop when it finds the last occurance of NoXXX and not start over at the top of the worksheet? Sub NextRow() ' ' Macro4 Macro ' ' Cells.Find(What:="NoXXX", After:=ActiveCell, LookIn:=xlValues, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stop a find after the last occrance of the search value.
I am assuming that you want the find in a loop so that it can find all
occurrences. If so then the following is adapted from an example in Help. Note that I have specified a range to look in and specified After:= ..cells(.cells.count). The reason for this is to ensure that the code initially looks after the last cell in the range which because it loops around to the top is actually the first cell of the range. The Findnext then finds the subsequent occurrences (if any). Sub testFind() Dim c As Range Dim firstAddress As String With Worksheets("Sheet1").Range("a1:a500") Set c = .Find(What:="NoXXX", _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False) If Not c Is Nothing Then firstAddress = c.Address Do MsgBox c.Address Set c = .FindNext(c) Loop While Not c Is Nothing _ And c.Address < firstAddress End If End With End Sub In lieu of the line With Worksheets("Sheet1").Range("a1:a500") you can use the following that includes all of the range that has been used on the worksheet. With Worksheets("Sheet1").UsedRange -- Regards, OssieMac "Pats" wrote: Is there any way to cause this routine to stop when it finds the last occurance of NoXXX and not start over at the top of the worksheet? Sub NextRow() ' ' Macro4 Macro ' ' Cells.Find(What:="NoXXX", After:=ActiveCell, LookIn:=xlValues, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stop a find after the last occurrence of the search value.
Thanks for your help, but I think I need to refine my question.
What I need is not to cycle through each occurrence of NoXXX but instead I need to go to the first occurrence of NoXXX, after the active cell, and select the cell containing NoXXX. If there is no additional occurrence of NoXXX I want the macro to stop. I do not want it to start looking above the active cell. "OssieMac" wrote in message ... I am assuming that you want the find in a loop so that it can find all occurrences. If so then the following is adapted from an example in Help. Note that I have specified a range to look in and specified After:= .cells(.cells.count). The reason for this is to ensure that the code initially looks after the last cell in the range which because it loops around to the top is actually the first cell of the range. The Findnext then finds the subsequent occurrences (if any). Sub testFind() Dim c As Range Dim firstAddress As String With Worksheets("Sheet1").Range("a1:a500") Set c = .Find(What:="NoXXX", _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False) If Not c Is Nothing Then firstAddress = c.Address Do MsgBox c.Address Set c = .FindNext(c) Loop While Not c Is Nothing _ And c.Address < firstAddress End If End With End Sub In lieu of the line With Worksheets("Sheet1").Range("a1:a500") you can use the following that includes all of the range that has been used on the worksheet. With Worksheets("Sheet1").UsedRange -- Regards, OssieMac "Pats" wrote: Is there any way to cause this routine to stop when it finds the last occurance of NoXXX and not start over at the top of the worksheet? Sub NextRow() ' ' Macro4 Macro ' ' Cells.Find(What:="NoXXX", After:=ActiveCell, LookIn:=xlValues, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stop a find after the last occurrence of the search value.
That turned out to be a harder request than I first thought it would be to
do (but fun to do, nonetheless<g). Just look at the last If..Then statement to see what I mean... it identifies all rows up to the row before the ActiveCell (accounting for the situation where the ActiveCell is on Row 1) and makes a Union of them coupled with all cells starting in Column A up to and including the ActiveCell's Column, and then Intersects that Union with the found cell to make sure then don't overlap). I marked a spot in the code where you can take action if you want to alert the user that there are no more cells to be found after the ActiveCell (with, perhaps, a MessageBox I would guess). Sub FindNext_NoXXX() Dim C As Range Set C = Cells.Find(What:="NoXXX", After:=ActiveCell, LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False, _ SearchFormat:=False) If Not C Is Nothing Then If Intersect(C, Union(Rows("1:" & (ActiveCell.Row + _ (ActiveCell.Row < 1))), Range("A" & _ ActiveCell.Row & ":" & ActiveCell.Address))) _ Is Nothing Then C.Select Else ' ' No more cells to be found... put any code you want here ' if you want to notify the user of that fact in some way ' End If End If End Sub -- Rick (MVP - Excel) "Patrick C. Simonds" wrote in message ... Thanks for your help, but I think I need to refine my question. What I need is not to cycle through each occurrence of NoXXX but instead I need to go to the first occurrence of NoXXX, after the active cell, and select the cell containing NoXXX. If there is no additional occurrence of NoXXX I want the macro to stop. I do not want it to start looking above the active cell. "OssieMac" wrote in message ... I am assuming that you want the find in a loop so that it can find all occurrences. If so then the following is adapted from an example in Help. Note that I have specified a range to look in and specified After:= .cells(.cells.count). The reason for this is to ensure that the code initially looks after the last cell in the range which because it loops around to the top is actually the first cell of the range. The Findnext then finds the subsequent occurrences (if any). Sub testFind() Dim c As Range Dim firstAddress As String With Worksheets("Sheet1").Range("a1:a500") Set c = .Find(What:="NoXXX", _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False) If Not c Is Nothing Then firstAddress = c.Address Do MsgBox c.Address Set c = .FindNext(c) Loop While Not c Is Nothing _ And c.Address < firstAddress End If End With End Sub In lieu of the line With Worksheets("Sheet1").Range("a1:a500") you can use the following that includes all of the range that has been used on the worksheet. With Worksheets("Sheet1").UsedRange -- Regards, OssieMac "Pats" wrote: Is there any way to cause this routine to stop when it finds the last occurance of NoXXX and not start over at the top of the worksheet? Sub NextRow() ' ' Macro4 Macro ' ' Cells.Find(What:="NoXXX", After:=ActiveCell, LookIn:=xlValues, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stop a find after the last occurrence of the search value.
Another way of doing this is instead of searching the entire worksheet is to
restrict the range being searched. Set the range from the activecell to the end of the worksheet. Sub FindAfterActivCell() Dim c As Range Dim rngToSearch As Range Set rngToSearch = Range(ActiveCell, _ Cells(Rows.Count, Columns.Count)) Set c = rngToSearch.Find(What:="NoXXX", _ After:=ActiveCell, _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False) If Not c Is Nothing Then c.Activate Else 'Your code here if not found MsgBox "Not found" End If End Sub -- Regards, OssieMac "Rick Rothstein" wrote: That turned out to be a harder request than I first thought it would be to do (but fun to do, nonetheless<g). Just look at the last If..Then statement to see what I mean... it identifies all rows up to the row before the ActiveCell (accounting for the situation where the ActiveCell is on Row 1) and makes a Union of them coupled with all cells starting in Column A up to and including the ActiveCell's Column, and then Intersects that Union with the found cell to make sure then don't overlap). I marked a spot in the code where you can take action if you want to alert the user that there are no more cells to be found after the ActiveCell (with, perhaps, a MessageBox I would guess). Sub FindNext_NoXXX() Dim C As Range Set C = Cells.Find(What:="NoXXX", After:=ActiveCell, LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False, _ SearchFormat:=False) If Not C Is Nothing Then If Intersect(C, Union(Rows("1:" & (ActiveCell.Row + _ (ActiveCell.Row < 1))), Range("A" & _ ActiveCell.Row & ":" & ActiveCell.Address))) _ Is Nothing Then C.Select Else ' ' No more cells to be found... put any code you want here ' if you want to notify the user of that fact in some way ' End If End If End Sub -- Rick (MVP - Excel) "Patrick C. Simonds" wrote in message ... Thanks for your help, but I think I need to refine my question. What I need is not to cycle through each occurrence of NoXXX but instead I need to go to the first occurrence of NoXXX, after the active cell, and select the cell containing NoXXX. If there is no additional occurrence of NoXXX I want the macro to stop. I do not want it to start looking above the active cell. "OssieMac" wrote in message ... I am assuming that you want the find in a loop so that it can find all occurrences. If so then the following is adapted from an example in Help. Note that I have specified a range to look in and specified After:= .cells(.cells.count). The reason for this is to ensure that the code initially looks after the last cell in the range which because it loops around to the top is actually the first cell of the range. The Findnext then finds the subsequent occurrences (if any). Sub testFind() Dim c As Range Dim firstAddress As String With Worksheets("Sheet1").Range("a1:a500") Set c = .Find(What:="NoXXX", _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False) If Not c Is Nothing Then firstAddress = c.Address Do MsgBox c.Address Set c = .FindNext(c) Loop While Not c Is Nothing _ And c.Address < firstAddress End If End With End Sub In lieu of the line With Worksheets("Sheet1").Range("a1:a500") you can use the following that includes all of the range that has been used on the worksheet. With Worksheets("Sheet1").UsedRange -- Regards, OssieMac "Pats" wrote: Is there any way to cause this routine to stop when it finds the last occurance of NoXXX and not start over at the top of the worksheet? Sub NextRow() ' ' Macro4 Macro ' ' Cells.Find(What:="NoXXX", After:=ActiveCell, LookIn:=xlValues, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stop a find after the last occurrence of the search value.
That won't work all the time. Put a single NoXXX on the worksheet, in say
B18, and make the Active Cell, say, D15... you won't find the NoXXX because it it in a column before the ActiveCell. -- Rick (MVP - Excel) "OssieMac" wrote in message ... Another way of doing this is instead of searching the entire worksheet is to restrict the range being searched. Set the range from the activecell to the end of the worksheet. Sub FindAfterActivCell() Dim c As Range Dim rngToSearch As Range Set rngToSearch = Range(ActiveCell, _ Cells(Rows.Count, Columns.Count)) Set c = rngToSearch.Find(What:="NoXXX", _ After:=ActiveCell, _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False) If Not c Is Nothing Then c.Activate Else 'Your code here if not found MsgBox "Not found" End If End Sub -- Regards, OssieMac "Rick Rothstein" wrote: That turned out to be a harder request than I first thought it would be to do (but fun to do, nonetheless<g). Just look at the last If..Then statement to see what I mean... it identifies all rows up to the row before the ActiveCell (accounting for the situation where the ActiveCell is on Row 1) and makes a Union of them coupled with all cells starting in Column A up to and including the ActiveCell's Column, and then Intersects that Union with the found cell to make sure then don't overlap). I marked a spot in the code where you can take action if you want to alert the user that there are no more cells to be found after the ActiveCell (with, perhaps, a MessageBox I would guess). Sub FindNext_NoXXX() Dim C As Range Set C = Cells.Find(What:="NoXXX", After:=ActiveCell, LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False, _ SearchFormat:=False) If Not C Is Nothing Then If Intersect(C, Union(Rows("1:" & (ActiveCell.Row + _ (ActiveCell.Row < 1))), Range("A" & _ ActiveCell.Row & ":" & ActiveCell.Address))) _ Is Nothing Then C.Select Else ' ' No more cells to be found... put any code you want here ' if you want to notify the user of that fact in some way ' End If End If End Sub -- Rick (MVP - Excel) "Patrick C. Simonds" wrote in message ... Thanks for your help, but I think I need to refine my question. What I need is not to cycle through each occurrence of NoXXX but instead I need to go to the first occurrence of NoXXX, after the active cell, and select the cell containing NoXXX. If there is no additional occurrence of NoXXX I want the macro to stop. I do not want it to start looking above the active cell. "OssieMac" wrote in message ... I am assuming that you want the find in a loop so that it can find all occurrences. If so then the following is adapted from an example in Help. Note that I have specified a range to look in and specified After:= .cells(.cells.count). The reason for this is to ensure that the code initially looks after the last cell in the range which because it loops around to the top is actually the first cell of the range. The Findnext then finds the subsequent occurrences (if any). Sub testFind() Dim c As Range Dim firstAddress As String With Worksheets("Sheet1").Range("a1:a500") Set c = .Find(What:="NoXXX", _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False) If Not c Is Nothing Then firstAddress = c.Address Do MsgBox c.Address Set c = .FindNext(c) Loop While Not c Is Nothing _ And c.Address < firstAddress End If End With End Sub In lieu of the line With Worksheets("Sheet1").Range("a1:a500") you can use the following that includes all of the range that has been used on the worksheet. With Worksheets("Sheet1").UsedRange -- Regards, OssieMac "Pats" wrote: Is there any way to cause this routine to stop when it finds the last occurance of NoXXX and not start over at the top of the worksheet? Sub NextRow() ' ' Macro4 Macro ' ' Cells.Find(What:="NoXXX", After:=ActiveCell, LookIn:=xlValues, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stop a find after the last occurrence of the search value.
A clarification... Because the OP set up the Find to search by rows, I
assumed he wanted to start looking from left to right across the row starting just after the ActiveCell and then wrap to the next row and continue searching the entire next row and so on until the bottom of the data was reached. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... That won't work all the time. Put a single NoXXX on the worksheet, in say B18, and make the Active Cell, say, D15... you won't find the NoXXX because it it in a column before the ActiveCell. -- Rick (MVP - Excel) "OssieMac" wrote in message ... Another way of doing this is instead of searching the entire worksheet is to restrict the range being searched. Set the range from the activecell to the end of the worksheet. Sub FindAfterActivCell() Dim c As Range Dim rngToSearch As Range Set rngToSearch = Range(ActiveCell, _ Cells(Rows.Count, Columns.Count)) Set c = rngToSearch.Find(What:="NoXXX", _ After:=ActiveCell, _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False) If Not c Is Nothing Then c.Activate Else 'Your code here if not found MsgBox "Not found" End If End Sub -- Regards, OssieMac "Rick Rothstein" wrote: That turned out to be a harder request than I first thought it would be to do (but fun to do, nonetheless<g). Just look at the last If..Then statement to see what I mean... it identifies all rows up to the row before the ActiveCell (accounting for the situation where the ActiveCell is on Row 1) and makes a Union of them coupled with all cells starting in Column A up to and including the ActiveCell's Column, and then Intersects that Union with the found cell to make sure then don't overlap). I marked a spot in the code where you can take action if you want to alert the user that there are no more cells to be found after the ActiveCell (with, perhaps, a MessageBox I would guess). Sub FindNext_NoXXX() Dim C As Range Set C = Cells.Find(What:="NoXXX", After:=ActiveCell, LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False, _ SearchFormat:=False) If Not C Is Nothing Then If Intersect(C, Union(Rows("1:" & (ActiveCell.Row + _ (ActiveCell.Row < 1))), Range("A" & _ ActiveCell.Row & ":" & ActiveCell.Address))) _ Is Nothing Then C.Select Else ' ' No more cells to be found... put any code you want here ' if you want to notify the user of that fact in some way ' End If End If End Sub -- Rick (MVP - Excel) "Patrick C. Simonds" wrote in message ... Thanks for your help, but I think I need to refine my question. What I need is not to cycle through each occurrence of NoXXX but instead I need to go to the first occurrence of NoXXX, after the active cell, and select the cell containing NoXXX. If there is no additional occurrence of NoXXX I want the macro to stop. I do not want it to start looking above the active cell. "OssieMac" wrote in message ... I am assuming that you want the find in a loop so that it can find all occurrences. If so then the following is adapted from an example in Help. Note that I have specified a range to look in and specified After:= .cells(.cells.count). The reason for this is to ensure that the code initially looks after the last cell in the range which because it loops around to the top is actually the first cell of the range. The Findnext then finds the subsequent occurrences (if any). Sub testFind() Dim c As Range Dim firstAddress As String With Worksheets("Sheet1").Range("a1:a500") Set c = .Find(What:="NoXXX", _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False) If Not c Is Nothing Then firstAddress = c.Address Do MsgBox c.Address Set c = .FindNext(c) Loop While Not c Is Nothing _ And c.Address < firstAddress End If End With End Sub In lieu of the line With Worksheets("Sheet1").Range("a1:a500") you can use the following that includes all of the range that has been used on the worksheet. With Worksheets("Sheet1").UsedRange -- Regards, OssieMac "Pats" wrote: Is there any way to cause this routine to stop when it finds the last occurance of NoXXX and not start over at the top of the worksheet? Sub NextRow() ' ' Macro4 Macro ' ' Cells.Find(What:="NoXXX", After:=ActiveCell, LookIn:=xlValues, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate End Sub |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stop a find after the last occurrence of the search value.
Yes. You are correct Rick so thanks for that.
Just for the exercise I changed the code to include the reminder of the row plus the remaining rows on the worksheet. I realize that setting the range to search using Union can be done all in one line of code but I always think it is easier to follow the code if it is separated as I have done in the example. Sub FindAfterActiveCell() 'Restrict the range being searched. Dim C As Range Dim rngToSearch1 As Range Dim rngToSearch2 As Range Dim rngToSearch As Range Set rngToSearch1 = _ Range(ActiveCell, Cells(ActiveCell.Row, Columns.Count)) Set rngToSearch2 = Range(Rows(ActiveCell.Row + 1), _ Rows(Rows.Count)) Set rngToSearch = Union(rngToSearch1, rngToSearch2) Set C = rngToSearch.Find(What:="NoXXX", _ After:=ActiveCell, _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False) If Not C Is Nothing Then C.Activate Else 'Your code here if not found MsgBox "Not found" End If End Sub -- Regards, OssieMac |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stop a find after the last occurrence of the search value.
Yes, that approach would work as well. As for setting two ranges first and
then taking the Union of them... I understand that preference completely, but my personal preference is to avoid creating variable that are not needed. In production code, I would include a comment explaining what my combined statement is doing. -- Rick (MVP - Excel) "OssieMac" wrote in message ... Yes. You are correct Rick so thanks for that. Just for the exercise I changed the code to include the reminder of the row plus the remaining rows on the worksheet. I realize that setting the range to search using Union can be done all in one line of code but I always think it is easier to follow the code if it is separated as I have done in the example. Sub FindAfterActiveCell() 'Restrict the range being searched. Dim C As Range Dim rngToSearch1 As Range Dim rngToSearch2 As Range Dim rngToSearch As Range Set rngToSearch1 = _ Range(ActiveCell, Cells(ActiveCell.Row, Columns.Count)) Set rngToSearch2 = Range(Rows(ActiveCell.Row + 1), _ Rows(Rows.Count)) Set rngToSearch = Union(rngToSearch1, rngToSearch2) Set C = rngToSearch.Find(What:="NoXXX", _ After:=ActiveCell, _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False) If Not C Is Nothing Then C.Activate Else 'Your code here if not found MsgBox "Not found" End If End Sub -- Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search Columns for a word and stop | Excel Discussion (Misc queries) | |||
Stop Search ---- Ron de Bruin RDB_Filter_Data | Excel Programming | |||
VB Code to search,copy,paste and stop at blank row! | Excel Programming | |||
How to stop search / replace prosess in large excel worksheets | Excel Programming | |||
search a string withing a string : find / search hangs | Excel Programming |