Pain in the ass macro
 

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

--
Jesse
-----------------------------------------------------------------------
JesseK's Profile: http://www.excelforum.com/member.php...fo&userid=2496
View this thread: http://www.excelforum.com/showthread.php?threadid=38492

Pain in the ass macro
 

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

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

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

--
steveB

Remove "AYN" from email to respond
"JesseK" wrote in
message ...

Im working on a macro that will go throgh an entire spreadsheet and take
out the "XXXX" in the formulas and replace it with a year giving me the
ability to create a report for any year. As of right now the only way
it will work is if I hardcode an actual year such as 2005 because the
find and replace I am running wont let me put a cell in the replace
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 to
do is replace the "2005" with a cell link so that before I run the macro
I can insert any year I want into that cell and then run the macro
giving me a report for any year I want. If its any help at all the cell
that the user inputs the year is L2, so some how 2005 must change to a
link to L2. Ive been messing with this for hours but nothing has
worked.

I would greatly appreciate any help at all. Thank you


--
JesseK
------------------------------------------------------------------------
JesseK's Profile:
http://www.excelforum.com/member.php...o&userid=24964
View this thread: http://www.excelforum.com/showthread...hreadid=384924

Pain in the ass macro
 

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

JesseK Wrote:
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

--
jose lui
-----------------------------------------------------------------------
jose luis's Profile: http://www.excelforum.com/member.php...fo&userid=1331
View this thread: http://www.excelforum.com/showthread.php?threadid=38492

Pain in the ass macro
 

Hi,

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


HTH

"JesseK" wrote:


Im working on a macro that will go throgh an entire spreadsheet and take
out the "XXXX" in the formulas and replace it with a year giving me the
ability to create a report for any year. As of right now the only way
it will work is if I hardcode an actual year such as 2005 because the
find and replace I am running wont let me put a cell in the replace
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 to
do is replace the "2005" with a cell link so that before I run the macro
I can insert any year I want into that cell and then run the macro
giving me a report for any year I want. If its any help at all the cell
that the user inputs the year is L2, so some how 2005 must change to a
link to L2. Ive been messing with this for hours but nothing has
worked.

I would greatly appreciate any help at all. Thank you


--
JesseK
------------------------------------------------------------------------
JesseK's Profile: http://www.excelforum.com/member.php...o&userid=24964
View this thread: http://www.excelforum.com/showthread...hreadid=384924

Pain in the ass macro
 

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" wrote:


Im working on a macro that will go throgh an entire spreadsheet and take
out the "XXXX" in the formulas and replace it with a year giving me the
ability to create a report for any year. As of right now the only way
it will work is if I hardcode an actual year such as 2005 because the
find and replace I am running wont let me put a cell in the replace
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 to
do is replace the "2005" with a cell link so that before I run the macro
I can insert any year I want into that cell and then run the macro
giving me a report for any year I want. If its any help at all the cell
that the user inputs the year is L2, so some how 2005 must change to a
link to L2. Ive been messing with this for hours but nothing has
worked.

I would greatly appreciate any help at all. Thank you


--
JesseK
------------------------------------------------------------------------
JesseK's Profile: http://www.excelforum.com/member.php...o&userid=24964
View this thread: http://www.excelforum.com/showthread...hreadid=384924

Pain in the ass macro
 

Thank you so much, it worked perfectly.


--
JesseK
------------------------------------------------------------------------
JesseK's Profile: http://www.excelforum.com/member.php...o&userid=24964
View this thread: http://www.excelforum.com/showthread...hreadid=384924