About Us

cdb

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Find and replace results can the appearance of find be different?	LLS at DPW	Excel Discussion (Misc queries)	2	October 26th 09 11:16 PM
Minus Key Results in EXT Message in Status Bar	cphalen	Excel Discussion (Misc queries)	8	November 19th 08 09:11 PM
Format results in Message Box	Jim G	Excel Discussion (Misc queries)	3	February 28th 07 12:03 AM
How to use HLOOKUP for different sheets depending on results from 2 cells	[email protected]	Excel Worksheet Functions	9	December 14th 06 11:15 PM
where to put results of find operation in find and replace functio	DEP	Excel Worksheet Functions	5	November 15th 06 07:52 PM

Menu

About Us