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?