Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help needed with FIND routine.
Dear All,
I'm a newbie to VBA, running Excel 2000. I have got myself very confused with a find routine that I am trying to write. I take a user input, a string, then I want to be able to look on three different worksheets to find the value and return the row number, of where the found item is. I then want to be able to repeat the routine, as desired with a find next type command. Advancing to the next worksheet once one has been searched. Can anybody please supply me with some sameple code to work with. TIA Matthew |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help needed with FIND routine.
It's amazing what you can find in HELP.
FindNext Method See Also Applies To Example Specifics Continues a search that was begun with the Find method. Finds the next cell that matches those same conditions and returns a Range object that represents that cell. Doesn't affect the selection or the active cell. expression.FindNext(After) expression Required. An expression that returns a Range object. After Optional Variant. The cell after which you want to search. This corresponds to the position of the active cell when a search is done from the user interface. Note that After must be a single cell in the range. Remember that the search begins after this cell; the specified cell isn't searched until the method wraps back around to this cell. If this argument isn't specified, the search starts after the cell in the upper-left corner of the range. Remarks When the search reaches the end of the specified search range, it wraps around to the beginning of the range. To stop a search when this wraparound occurs, save the address of the first found cell, and then test each successive found-cell address against this saved address. Example This example finds all cells in the range A1:A500 that contain the value 2 and changes their values to 5. With Worksheets(1).Range("a1:a500") Set c = .Find(2, lookin:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do c.Value = 5 Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With"Matthew" wrote in message ... Dear All, I'm a newbie to VBA, running Excel 2000. I have got myself very confused with a find routine that I am trying to write. I take a user input, a string, then I want to be able to look on three different worksheets to find the value and return the row number, of where the found item is. I then want to be able to repeat the routine, as desired with a find next type command. Advancing to the next worksheet once one has been searched. Can anybody please supply me with some sameple code to work with. TIA Matthew |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help needed with FIND routine.
so just to add to the example:
Dim sStr as String Dim c as Range Dim firstAddress as String sStr = "ABCD" for each ws in Worksheets(Array("Sheet1","Sheet2","Sheet3")) firstAddress = "" set c = Nothing With Ws.Cells Set c = .Find(sStr, lookin:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do application.Goto Reference:=c, Scroll:=True msgbox "found at " & c.row & " on Sheet: " & ws.Name Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With Next ws -- Regards, Tom Ogilvy Don Guillett wrote in message ... It's amazing what you can find in HELP. FindNext Method See Also Applies To Example Specifics Continues a search that was begun with the Find method. Finds the next cell that matches those same conditions and returns a Range object that represents that cell. Doesn't affect the selection or the active cell. expression.FindNext(After) expression Required. An expression that returns a Range object. After Optional Variant. The cell after which you want to search. This corresponds to the position of the active cell when a search is done from the user interface. Note that After must be a single cell in the range. Remember that the search begins after this cell; the specified cell isn't searched until the method wraps back around to this cell. If this argument isn't specified, the search starts after the cell in the upper-left corner of the range. Remarks When the search reaches the end of the specified search range, it wraps around to the beginning of the range. To stop a search when this wraparound occurs, save the address of the first found cell, and then test each successive found-cell address against this saved address. Example This example finds all cells in the range A1:A500 that contain the value 2 and changes their values to 5. With Worksheets(1).Range("a1:a500") Set c = .Find(2, lookin:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do c.Value = 5 Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With"Matthew" wrote in message ... Dear All, I'm a newbie to VBA, running Excel 2000. I have got myself very confused with a find routine that I am trying to write. I take a user input, a string, then I want to be able to look on three different worksheets to find the value and return the row number, of where the found item is. I then want to be able to repeat the routine, as desired with a find next type command. Advancing to the next worksheet once one has been searched. Can anybody please supply me with some sameple code to work with. TIA Matthew |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help needed with FIND routine.
Tom, thanks!
A couple of tweaks to suit my system and it works a treat, I was stuck on the selecting multiple sheets for my range and also returing the row reference for the found items. I am still a little confused about the Set command. What is the difference between: set c = Nothing and c = Nothing Thanks again Matthew -----Original Message----- so just to add to the example: Dim sStr as String Dim c as Range Dim firstAddress as String sStr = "ABCD" for each ws in Worksheets(Array ("Sheet1","Sheet2","Sheet3")) firstAddress = "" set c = Nothing With Ws.Cells Set c = .Find(sStr, lookin:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do application.Goto Reference:=c, Scroll:=True msgbox "found at " & c.row & " on Sheet: " & ws.Name Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With Next ws -- Regards, Tom Ogilvy Don Guillett wrote in message ... It's amazing what you can find in HELP. FindNext Method See Also Applies To Example Specifics Continues a search that was begun with the Find method. Finds the next cell that matches those same conditions and returns a Range object that represents that cell. Doesn't affect the selection or the active cell. expression.FindNext(After) expression Required. An expression that returns a Range object. After Optional Variant. The cell after which you want to search. This corresponds to the position of the active cell when a search is done from the user interface. Note that After must be a single cell in the range. Remember that the search begins after this cell; the specified cell isn't searched until the method wraps back around to this cell. If this argument isn't specified, the search starts after the cell in the upper-left corner of the range. Remarks When the search reaches the end of the specified search range, it wraps around to the beginning of the range. To stop a search when this wraparound occurs, save the address of the first found cell, and then test each successive found-cell address against this saved address. Example This example finds all cells in the range A1:A500 that contain the value 2 and changes their values to 5. With Worksheets(1).Range("a1:a500") Set c = .Find(2, lookin:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do c.Value = 5 Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With"Matthew" wrote in message ... Dear All, I'm a newbie to VBA, running Excel 2000. I have got myself very confused with a find routine that I am trying to write. I take a user input, a string, then I want to be able to look on three different worksheets to find the value and return the row number, of where the found item is. I then want to be able to repeat the routine, as desired with a find next type command. Advancing to the next worksheet once one has been searched. Can anybody please supply me with some sameple code to work with. TIA Matthew . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help needed with FIND routine.
c = Nothing
raises an error. You need set to assign a reference to an object - in this case you are setting a reference to nothing - in otherwords, the object variable does not have a reference If you want to test for that condition If c is nothing then -- Regards, Tom Ogilvy Matthew wrote in message ... Tom, thanks! A couple of tweaks to suit my system and it works a treat, I was stuck on the selecting multiple sheets for my range and also returing the row reference for the found items. I am still a little confused about the Set command. What is the difference between: set c = Nothing and c = Nothing Thanks again Matthew -----Original Message----- so just to add to the example: Dim sStr as String Dim c as Range Dim firstAddress as String sStr = "ABCD" for each ws in Worksheets(Array ("Sheet1","Sheet2","Sheet3")) firstAddress = "" set c = Nothing With Ws.Cells Set c = .Find(sStr, lookin:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do application.Goto Reference:=c, Scroll:=True msgbox "found at " & c.row & " on Sheet: " & ws.Name Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With Next ws -- Regards, Tom Ogilvy Don Guillett wrote in message ... It's amazing what you can find in HELP. FindNext Method See Also Applies To Example Specifics Continues a search that was begun with the Find method. Finds the next cell that matches those same conditions and returns a Range object that represents that cell. Doesn't affect the selection or the active cell. expression.FindNext(After) expression Required. An expression that returns a Range object. After Optional Variant. The cell after which you want to search. This corresponds to the position of the active cell when a search is done from the user interface. Note that After must be a single cell in the range. Remember that the search begins after this cell; the specified cell isn't searched until the method wraps back around to this cell. If this argument isn't specified, the search starts after the cell in the upper-left corner of the range. Remarks When the search reaches the end of the specified search range, it wraps around to the beginning of the range. To stop a search when this wraparound occurs, save the address of the first found cell, and then test each successive found-cell address against this saved address. Example This example finds all cells in the range A1:A500 that contain the value 2 and changes their values to 5. With Worksheets(1).Range("a1:a500") Set c = .Find(2, lookin:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do c.Value = 5 Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With"Matthew" wrote in message ... Dear All, I'm a newbie to VBA, running Excel 2000. I have got myself very confused with a find routine that I am trying to write. I take a user input, a string, then I want to be able to look on three different worksheets to find the value and return the row number, of where the found item is. I then want to be able to repeat the routine, as desired with a find next type command. Advancing to the next worksheet once one has been searched. Can anybody please supply me with some sameple code to work with. TIA Matthew . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help needed to find a formula | Excel Discussion (Misc queries) | |||
Routine to find exact Row matches in Col1 Col2 Col3 but exact offsetting numbers in Col4 | Excel Discussion (Misc queries) | |||
FIND formula help needed | Excel Discussion (Misc queries) | |||
Macro needed (like find) | Excel Discussion (Misc queries) | |||
Print routine needed for code | Excel Worksheet Functions |