Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find across several sheets and put results in a message box
I am trying to write a macro that will search across several sheets unsing the find function, and then if it finds the right thing, to concatenate that with the 2 columns before it.
I have some code that works with does the find part, which is: Sub SearchAllv2() Dim SearchReq As String, FoundReq As String, Location As String, Recruiter As String SearchReq = InputBox("Enter the Requisition you are looking for", "Requisition Search") Sheets(Array("Sheet 1", "Sheet 2", "Sheet 3", "Sheet 4")).Select Sheets("Sheet 1").Activate Cells.Select SendKeys ("^f") 'Open find box SendKeys (SearchReq) 'enter data from input box SendKeys ("{ENTER}") 'start search SendKeys ("{ESC}") 'close find box SendKeys ("{ESC}") 'close find box if error message appeared FoundReq = ActiveCell.Value SendKeys ("{LEFT}") Recruiter = ActiveCell.Value SendKeys ("{LEFT}") Location = ActiveCell.Value The above code works fine until I add anything else in. The code I am trying to add is just to concatenate them and put them in a message box. I am trying to use: MsgBox (FoundReq & " " & Recruiter & " " & Loc) If anyone can help with this, and also a way to put an error message up if the item is not found? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find across several sheets and put results in a message box
Hi Cdb,
I am trying to write a macro that will search across several sheets unsing the find function, and then if it finds the right thing, to concatenate that with the 2 columns before it. You are using sendkeys, which is hardly ever needed and very error prone. Here is some code I wrote a few years back that does the finding work: Sub FindItAll() Dim oSheet As Object Dim Firstcell As Range Dim NextCell As Range Dim WhatToFind As Variant WhatToFind = Application.InputBox("What are you looking for ?", "Search", , 100, 100, , , 2) If WhatToFind < "" And Not WhatToFind = False Then For Each oSheet In ActiveWorkbook.Worksheets oSheet.Activate oSheet.[a1].Activate Set Firstcell = Cells.Find(What:=WhatToFind, LookIn:=xlValues, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) If Not Firstcell Is Nothing Then Firstcell.Activate MsgBox ("Found " & Chr(34) & WhatToFind & Chr(34) & " in " & oSheet.Name & "!" & Firstcell.Address) On Error Resume Next While (Not NextCell Is Nothing) And (Not NextCell.Address = Firstcell.Address) Set NextCell = Cells.FindNext(After:=ActiveCell) If Not NextCell.Address = Firstcell.Address Then NextCell.Activate MsgBox ("Found " & Chr(34) & WhatToFind & Chr(34) & " in " & oSheet.Name & "!" & NextCell.Address) End If Wend End If Set NextCell = Nothing Set Firstcell = Nothing Next oSheet End If End Sub At the message box near the end of this code you can insert the part that does what you need. Regards, Jan Karel Pieterse Excel MVP www.jkp-ads.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find across several sheets and put results in a message box
Many thanks - I'll give it a try.
"Jan Karel Pieterse" wrote: Hi Cdb, I am trying to write a macro that will search across several sheets unsing the find function, and then if it finds the right thing, to concatenate that with the 2 columns before it. You are using sendkeys, which is hardly ever needed and very error prone. Here is some code I wrote a few years back that does the finding work: Sub FindItAll() Dim oSheet As Object Dim Firstcell As Range Dim NextCell As Range Dim WhatToFind As Variant WhatToFind = Application.InputBox("What are you looking for ?", "Search", , 100, 100, , , 2) If WhatToFind < "" And Not WhatToFind = False Then For Each oSheet In ActiveWorkbook.Worksheets oSheet.Activate oSheet.[a1].Activate Set Firstcell = Cells.Find(What:=WhatToFind, LookIn:=xlValues, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) If Not Firstcell Is Nothing Then Firstcell.Activate MsgBox ("Found " & Chr(34) & WhatToFind & Chr(34) & " in " & oSheet.Name & "!" & Firstcell.Address) On Error Resume Next While (Not NextCell Is Nothing) And (Not NextCell.Address = Firstcell.Address) Set NextCell = Cells.FindNext(After:=ActiveCell) If Not NextCell.Address = Firstcell.Address Then NextCell.Activate MsgBox ("Found " & Chr(34) & WhatToFind & Chr(34) & " in " & oSheet.Name & "!" & NextCell.Address) End If Wend End If Set NextCell = Nothing Set Firstcell = Nothing Next oSheet End If End Sub At the message box near the end of this code you can insert the part that does what you need. Regards, Jan Karel Pieterse Excel MVP www.jkp-ads.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find and replace results can the appearance of find be different? | Excel Discussion (Misc queries) | |||
Minus Key Results in EXT Message in Status Bar | Excel Discussion (Misc queries) | |||
Format results in Message Box | Excel Discussion (Misc queries) | |||
How to use HLOOKUP for different sheets depending on results from 2 cells | Excel Worksheet Functions | |||
where to put results of find operation in find and replace functio | Excel Worksheet Functions |