MACRO using Find with a Copy/Paste

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Howdy

I'm trying to do a macro that will g to a certain cell, COPY the contents, switch to another sheet, do a Edit/Find, PASTE the data into the box and do the search

When I just do a "Record Macro" it works fine but it doesn't record that I'm PASTING the data into the find box. It just records what I ended up doing the search for so the macro gets hard coded to search for whatever I happened to use when recording it

How can I make this work so it will search for whatever is in a specific cell at the time the macro is run

Thanks!
 
Mr B,

No need to copy. If the cell with the value to replace is on Sheet1, say
cell B3, and you want to do the replace on Sheet2, then

Sheets("Sheet2").Cells.Replace _
What:=Worksheets("Sheet1").Range("B3").Value, _
Replacement:="What to Replace with", _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
MatchCase:=False


HTH,
Bernie
MS Excel MVP

Mr B said:
Howdy,

I'm trying to do a macro that will g to a certain cell, COPY the contents,
Click to expand...
switch to another sheet, do a Edit/Find, PASTE the data into the box and do
the search.
When I just do a "Record Macro" it works fine but it doesn't record that
Click to expand...
I'm PASTING the data into the find box. It just records what I ended up
doing the search for so the macro gets hard coded to search for whatever I
happened to use when recording it.
How can I make this work so it will search for whatever is in a specific
Click to expand...
cell at the time the macro is run?
 
i have a little idea as to how to go about doing it. i can give you th
algorithm, though i have not the initiative to write the piece o
code.

there are two methods called putinclipboard and getfromclipboard.

putinclipboard could be used as:

SomeText.settext "cell-text"
SomeText.putinclipboard

the getfromclipboard method can be assigned to a string, which in tur
could be supplanted into the "what:=" section of the code.

getfromclipboard could be used as:

SomeText.getfromclipboard
string = SomeText.gettext

as i mentioned, i have not tried this. so dont hang me, if it does no
work
 
Mr B,

Sub NewSub()
Dim myCell As Range
Set myCell = Sheets("Sheet2").Cells.Find _
(Worksheets("Sheet1").Range("C6").Value, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
MatchCase:=False)
myCell.Parent.Activate
myCell.Select
End Sub

HTH,
Bernie
MS Excel MVP

Mr B said:
I'm not actually doing a replace, just a find so I can get to the right row on Sheet 2.

Specifically the primary cell is Sheet1:C6. I need to go to Sheet 2 and
Click to expand...
jump to the cell that has the same value. No replacing or anything, just
need that cell to be selected so I can move around from there.
 
Back
Top