Excel'97 Macro with functions Problem

[Joe H II]

I am trying to create a macro to start on sheet1, copy a cell that th
user will input a # into, go to sheet3, search for that #, and i
found, cut and paste that row from sheet3 into a new row in sheet2. I
the # is not found, it needs to be pasted into the appropriate cell i
a new row on sheet2

My problem is that when I'm recording the macro and go to sheet3 an
perform the "find" function and paste the # from sheet1, it looks fo
the exact #; not a cell reference (see Example)

Range("B6").Select
Selection.Copy
Sheets("3 Historical").Select
Cells.Find(What:="PRK04070", After:=ActiveCell, LookIn:=xlFormulas
_
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext
_
MatchCase:=False).Activate
End Su

 

[Bob Phillips]

Joe,

Try something like

Sheets("3
Historical").Cells.Find(What:=Worksheets("Sheet1").Range("B6").Value, _
After:=ActiveCell, +
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False).Activate
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Joe H II]

That was what I was trying to get to, thanks, but now I am gettin
errors. I guess I should have specified that I don't really know how t
Program (if you haven't already figured that out). I get "Objec
variable With block variable not set". and when I choose to debug th
macro, all it does is highlight the section from "cells" down t
"activate

 

[Bob Phillips]

Joe,

Create a stand-alone macro like this

Worksheets("3 Historical").Activate
Worksheets("3 Historical").Cells.Find(_ _

What:=Worksheets("Sheet1").Range("B6").Value, _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False).Activate
End Sub

and test it and see what you get.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)