Find and Replace with a cell reference

[raichu.526]

Hi all -

I would like to write a macro that essentially does a Find and
Replace, but instead of replacing with a string, it replaces with a
reference to a cell that contains that string.

As an example, the meat of the macro currently looks something like
this:

Selection.Replace What:="DATE", Replacement:="1234567", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False

So instead of replacing with "1234567", I want the Replacement to
point to the cell that has "1234567". The purpose is to update a
workbook that draws data from other workbooks that are differentiated
by date. The template has cell references to a file that contains the
word "DATE" (no such file actually exists). Running the macro would
replace "DATE" with the date that's been keyed in elsewhere on the
same sheet, and would turn the cell references into valid ones.

Thanks for any help that comes along.

 

[Gord Dibben]

Maybe..........?

Dim mydate As Range
Dim rng As Range
Set mydate = ActiveSheet.Range("B43")
For Each rng In ActiveSheet.UsedRange
rng.Replace What:="DATE", Replacement:=mydate.Value, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
Next


Gord Dibben MS Excel MVP

 

[Excel]

I read Gord Dibben's response but wouldn't that just replace each
"DATE", with the same date, i.e. the one that is in cell B43? (I'm
not a VB expert... or close).

I'm figuring if you have columns that look like this

blahDATEblah 20070302
blahDATEblah 20081121

You might create a third column that replaces the "DATE" with the item
in the second column. The functions you need to make that happen is
"FIND", "LEFT", "RIGHT" and use the "&" symbol to string texts
together;

Something *Like* this.
=left(a1,find("date",a1)-1)&B1&right(a1,find("Date",a1+4))
If the date column is formatted differently, you'll need the TEXT
function, too.

Does this help?

 

[raichu.526]

Gord's suggestion looked similar to others I've seen, but at first it
did not work. After some trial and error, I got the desired results
when I replaced

Set mydate = ActiveSheet.Range...

with

Set mydate = ActiveCell.Range...

This necessitates running the macro using relative references and
positioning the active cell before each run, but that's a
comparatively simple matter.

Thanks for the replies!