Find across several sheets and put results in a message box

[Guest]

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?

 

[Jan Karel Pieterse]

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

 

[Guest]

Many thanks - I'll give it a try.

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