View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
K Dales[_2_] K Dales[_2_] is offline
external usenet poster
 
Posts: 1,163
Default 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