VBA code in Excel to "paste" data into FIND dialog Box

[eguthriedba]

I am trying to compile a simple visual basic macro in Excel that copies or uses the data from cell "A1" in Sheet 1, pastes (or by some other means populates) the data into the "Find" dialog box in Excel, and then attempts to find the data in Sheet 2. The particular probelm I am having in the VBA code is instructing the find function to look at or use the data in cell "A1" of Sheet 1 for the search in Sheet 2. The code for the "Find" function presently looks like this:

Cells.Find(What:=?????????, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

Essentially, I need the string of question marks to be replaced by an instruction to pull the data from cell "A1" on Sheet 1.

Here is the entire code just for reference:

Range("A1").Select
ActiveSheet.Next.Select
Range("A2").Select
ActiveSheet.Previous.Select
Selection.Copy
ActiveSheet.Next.Select
Cells.Find(What:=?????????, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Selection.Interior.ColorIndex = 45
ActiveSheet.Previous.Select
Rows("1:1").Select
Selection.Delete Shift:=xlUp

Thanks Tek-Tips for everything you do!!

 

[SkipVought]

Hi,

Please post VBA questions in forum707.

    Dim r As Range
    
    Set r = Sheet2.Cells.Find(What:=Sheet1.[A1], After:=Sheet2.[A1], LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)

    MsgBox "Sheet " & r.Parent.Name & " address " & r.Address

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}