Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Find all" and show the cell addresses in a MsgBox
I need to perform a "Find" search in two sheets and then show the addresses
of the matches in a MsgBox. I'm running XL2000 ( No Potions in the Find dialogbox) I think I can create an array with the results and then show each element as a line in the MsgBox, problem: I don't know how to do it.... Thanks in advance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Find all" and show the cell addresses in a MsgBox
Maybe this function helps you with what you are trying to do:
http://www.ozgrid.com/forum/showthread.php?t=27240 hth Carlo On Dec 19, 12:10 pm, LuisE wrote: I need to perform a "Find" search in two sheets and then show the addresses of the matches in a MsgBox. I'm running XL2000 ( No Potions in the Find dialogbox) I think I can create an array with the results and then show each element as a line in the MsgBox, problem: I don't know how to do it.... Thanks in advance |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Find all" and show the cell addresses in a MsgBox
On 19 Dec., 04:10, LuisE wrote:
I need to perform a "Find" search in two sheets and then show the addresses of the matches in a MsgBox. I'm running XL2000 ( No Potions in the Find dialogbox) I think I can create an array with the results and then show each element as a line in the MsgBox, problem: I don't know how to do it.... Thanks in advance Sub Find() SearchString = "Text" ' Fit to suit i = 1 Sheets(1).Select Range("A1").Select Cells.Find(What:=SearchString, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _ .Activate Sh1String = ActiveCell.Address FirstHit = ActiveCell.Address Do Cells.FindNext(After:=ActiveCell).Activate Sh1String = Sh1String & ", " & ActiveCell.Address i = i + 1 Loop Until FirstHit = ActiveCell.Address Sh1String = Left(Sh1String, Len(Sh1String) - (Len(FirstHit) + 2)) MsgBox Sh1String End Sub Regards, Per |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Find all" and show the cell addresses in a MsgBox
Two things... Your solution will crash if no instance of the SearchString is
found and secondly don't use Find as the name of your sub. Find is a reserved word an you might find that things can get messed up using that as the sub name. -- HTH... Jim Thomlinson "Per Jessen" wrote: On 19 Dec., 04:10, LuisE wrote: I need to perform a "Find" search in two sheets and then show the addresses of the matches in a MsgBox. I'm running XL2000 ( No Potions in the Find dialogbox) I think I can create an array with the results and then show each element as a line in the MsgBox, problem: I don't know how to do it.... Thanks in advance Sub Find() SearchString = "Text" ' Fit to suit i = 1 Sheets(1).Select Range("A1").Select Cells.Find(What:=SearchString, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _ .Activate Sh1String = ActiveCell.Address FirstHit = ActiveCell.Address Do Cells.FindNext(After:=ActiveCell).Activate Sh1String = Sh1String & ", " & ActiveCell.Address i = i + 1 Loop Until FirstHit = ActiveCell.Address Sh1String = Left(Sh1String, Len(Sh1String) - (Len(FirstHit) + 2)) MsgBox Sh1String End Sub Regards, Per |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Find all" and show the cell addresses in a MsgBox
Something like this should do...
Sub FindAll() Call FindStuff(Sheets("Sheet1"), "This") 'change to suit Call FindStuff(Sheets("Sheet2"), "This") End Sub Sub FindStuff(ByVal wks As Worksheet, ByVal strToFind As String) Dim rngFound As Range Dim rngFoundAll As Range Dim rngToSearch As Range Dim strFirstAddress As String Set rngToSearch = wks.Range("A:A") 'change to suit Set rngFound = rngToSearch.Find(What:=strToFind, _ LookAt:=xlWhole, _ LookIn:=xlFormulas, _ MatchCase:=False) If rngFound Is Nothing Then MsgBox strToFind & " could not be found in " & wks.Name Else Set rngFoundAll = rngFound strFirstAddress = rngFound.Address Do Set rngFoundAll = Union(rngFound, rngFoundAll) Set rngFound = rngToSearch.FindNext(rngFound) Loop Until rngFound.Address = strFirstAddress MsgBox rngFoundAll.Address(External:=True) End If End Sub -- HTH... Jim Thomlinson "LuisE" wrote: I need to perform a "Find" search in two sheets and then show the addresses of the matches in a MsgBox. I'm running XL2000 ( No Potions in the Find dialogbox) I think I can create an array with the results and then show each element as a line in the MsgBox, problem: I don't know how to do it.... Thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
How do I find the "Show Pages..." Pivot table feature in 2007? | Excel Discussion (Misc queries) | |||
Best way to trap error to MsgBox "Too many cell formats" | Excel Discussion (Misc queries) | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
if "a" selected from dropdown menu then show "K" in other cell | Excel Worksheet Functions |