Pain in the ass macro

[JesseK]

Im working on a macro that will go throgh an entire spreadsheet and tak
out the "XXXX" in the formulas and replace it with a year giving me th
ability to create a report for any year. As of right now the only wa
it will work is if I hardcode an actual year such as 2005 because th
find and replace I am running wont let me put a cell in the replac
secton instead of a defined number.

Cells.Replace What:="XXXX", Replacement:="2005", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

That is the macro code for the find and replace secton, what i need t
do is replace the "2005" with a cell link so that before I run the macr
I can insert any year I want into that cell and then run the macr
giving me a report for any year I want. If its any help at all the cel
that the user inputs the year is L2, so some how 2005 must change to
link to L2. Ive been messing with this for hours but nothing ha
worked.

I would greatly appreciate any help at all. Thank yo

 

[STEVE BELL]

Creat a variable and use it instead of "2005"
myvariable = ?????

Cells.Replace What:="XXXX", Replacement:="2005",

Cells.Replace What:="XXXX", Replacement:=myvariable,

 

[jose luis]

Hi Jesse,


To stop your pain, (in that part) you could use this:


Code
-------------------
Cells.Replace What:="XXXX", Replacement:=Range("L2"), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=Fals
-------------------


Hope this reliefs your soul.


Best Regards

Jose Luis

 

[Guest]

Hi,

Cells.Replace What:="XXXX", Replacement:=Range("L2"), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False


HTH

 

[Guest]

You can have the same effect with no macro needed at all. Choose the cell
where you want to put the year and give the cell a name - call it something
like ReportYear. Then do your find and replace (manually, just this once)
and replace your "XXXX" with "ReportYear". Now the formulas all refer to the
contents of that cell and as soon as you change the cell the formulas should
update.

 

[JesseK]

Thank you so much, it worked perfectly.