Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use Range Name with 'Find'
And you want to select the first cell that's found?
Dim foundcell As Range dim myVals as Variant dim iCtr as long myVals = array(22,13,44) With ActiveSheet for ictr = lbound(myVals) to ubound(myvals) 'might as well use that named range Set foundcell = .Range("Priced").Find(What:=myvals(ictr)) If foundcell Is Nothing Then MsgBox "not found" Else foundcell.Select exit for End If next ictr End With ps. I think that you would be much better served by supplying all the parms to the .find command. .Find will remember the settings that were last used--either in code or by the user. For example, if you want xlwhole, then specify it. The user may have changed the parm to xlPart. And your code may not do what you want. ps. Watch out for typos. I composed in the email window. Paige wrote: I have a range name ('Priced') that includes multiple values (like 22, 13, 44, etc.), and want to be able to find any of the values included in this range name. What I have below finds only the number '22'; how can I modify it to find any of the numbers found in the range name? Have tried numerous ways to do this, but am obviously missing the correct syntax. P.S. The range name is on another sheet within the same file. Dim foundcell As Range With ActiveSheet Set foundcell = .Range("o3:o65536").Find(What:=22) If foundcell Is Nothing Then MsgBox "not found" Else foundcell.Select End If End With Thanks....Paige -- Dave Peterson |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use Range Name with 'Find'
You may want to comment that "msgbox" line. It can become irritatin'.
Or you may want to make it more meaningful: msgbox myvals(ictr) & " wasn't found" Paige wrote: Hi, Dave...yes. Thanks! "Dave Peterson" wrote: And you want to select the first cell that's found? Dim foundcell As Range dim myVals as Variant dim iCtr as long myVals = array(22,13,44) With ActiveSheet for ictr = lbound(myVals) to ubound(myvals) 'might as well use that named range Set foundcell = .Range("Priced").Find(What:=myvals(ictr)) If foundcell Is Nothing Then MsgBox "not found" Else foundcell.Select exit for End If next ictr End With ps. I think that you would be much better served by supplying all the parms to the .find command. .Find will remember the settings that were last used--either in code or by the user. For example, if you want xlwhole, then specify it. The user may have changed the parm to xlPart. And your code may not do what you want. ps. Watch out for typos. I composed in the email window. Paige wrote: I have a range name ('Priced') that includes multiple values (like 22, 13, 44, etc.), and want to be able to find any of the values included in this range name. What I have below finds only the number '22'; how can I modify it to find any of the numbers found in the range name? Have tried numerous ways to do this, but am obviously missing the correct syntax. P.S. The range name is on another sheet within the same file. Dim foundcell As Range With ActiveSheet Set foundcell = .Range("o3:o65536").Find(What:=22) If foundcell Is Nothing Then MsgBox "not found" Else foundcell.Select End If End With Thanks....Paige -- Dave Peterson -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use Range Name with 'Find'
Good idea - will do! Thanks again; have a good evening.
"Dave Peterson" wrote: You may want to comment that "msgbox" line. It can become irritatin'. Or you may want to make it more meaningful: msgbox myvals(ictr) & " wasn't found" Paige wrote: Hi, Dave...yes. Thanks! "Dave Peterson" wrote: And you want to select the first cell that's found? Dim foundcell As Range dim myVals as Variant dim iCtr as long myVals = array(22,13,44) With ActiveSheet for ictr = lbound(myVals) to ubound(myvals) 'might as well use that named range Set foundcell = .Range("Priced").Find(What:=myvals(ictr)) If foundcell Is Nothing Then MsgBox "not found" Else foundcell.Select exit for End If next ictr End With ps. I think that you would be much better served by supplying all the parms to the .find command. .Find will remember the settings that were last used--either in code or by the user. For example, if you want xlwhole, then specify it. The user may have changed the parm to xlPart. And your code may not do what you want. ps. Watch out for typos. I composed in the email window. Paige wrote: I have a range name ('Priced') that includes multiple values (like 22, 13, 44, etc.), and want to be able to find any of the values included in this range name. What I have below finds only the number '22'; how can I modify it to find any of the numbers found in the range name? Have tried numerous ways to do this, but am obviously missing the correct syntax. P.S. The range name is on another sheet within the same file. Dim foundcell As Range With ActiveSheet Set foundcell = .Range("o3:o65536").Find(What:=22) If foundcell Is Nothing Then MsgBox "not found" Else foundcell.Select End If End With Thanks....Paige -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Last cell in Range when range is date format | Excel Discussion (Misc queries) | |||
find within a named range, then deselect the range | Excel Programming | |||
Find the POSITION IN A RANGE of text in a string that matches value(s) in a range | Excel Programming | |||
Find dates in a range; then sum values in that range by a criteria | Excel Discussion (Misc queries) | |||
Find first cell in range and expand range -VBA | Excel Programming |