1) Can you make a macro that searches for text and then replace that text
with other text?
2) If so, can you make this macro look for several different text and
replace them all with the same text?
3) Can you make a macro that replaces parts of a file name in the reference
portion of a formula?

Yes to all three. If you supply actual details, someone here can show you
how to write the macros for them.

--
Rick (MVP - Excel)


"Chad" wrote in message
...
1) Can you make a macro that searches for text and then replace that text
with other text?
2) If so, can you make this macro look for several different text and
replace them all with the same text?
3) Can you make a macro that replaces parts of a file name in the
reference
portion of a formula?

"Rick Rothstein" wrote:

Yes to all three. If you supply actual details, someone here can show you
how to write the macros for them.

--
Rick (MVP - Excel)

Ok More info here. Basically I have a cell in a file named "Yearly numbers"
and it links to a file named "January numbers". Each month the person at my
job that updates the monthly numbers just changes the numbers and renames the
file to the new month like "Feburary numbers". So want I need to do is write
a macro that searches all formulas for the name of any month and change that
month to the current month. And I would like it to do that everytime you open
the file so it is automated.

Any chance you can change all formulas that contain a month name to use a
cell reference instead of the text month? For example, if you had a formula
in your workbook like this...

=IF(B2<"","January's value is"&B2","")

and A1 was a currently unused cell, You could write January in A1 and change
the formula to this...

=IF(B2<"",A1&"'s value is"&B2","")

If you made similar changes to all your formulas that specified the month's
name in text form, then just changing A1 would automatically update all the
formulas at once. This would be much faster than executing a macro that
looked at, and changed, each formula with the month name in text form. If
you can do this, fine. If not, then are there more than one worksheet in
your workbook?

--
Rick (MVP - Excel)


"Chad" wrote in message
...


"Rick Rothstein" wrote:

Yes to all three. If you supply actual details, someone here can show you
how to write the macros for them.

--
Rick (MVP - Excel)

Ok More info here. Basically I have a cell in a file named "Yearly
numbers"
and it links to a file named "January numbers". Each month the person at
my
job that updates the monthly numbers just changes the numbers and renames
the
file to the new month like "Feburary numbers". So want I need to do is
write
a macro that searches all formulas for the name of any month and change
that
month to the current month. And I would like it to do that everytime you
open
the file so it is automated.

"Rick Rothstein" wrote:

Any chance you can change all formulas that contain a month name to use a
cell reference instead of the text month? For example, if you had a formula
in your workbook like this...

=IF(B2<"","January's value is"&B2","")

and A1 was a currently unused cell, You could write January in A1 and change
the formula to this...

=IF(B2<"",A1&"'s value is"&B2","")

If you made similar changes to all your formulas that specified the month's
name in text form, then just changing A1 would automatically update all the
formulas at once. This would be much faster than executing a macro that
looked at, and changed, each formula with the month name in text form. If
you can do this, fine. If not, then are there more than one worksheet in
your workbook?

--
Rick (MVP - Excel)


Do not think I can do that cause the text for the month is part of a file
name such as "January numbers.xls" EX. one formula is this
=IF(ISERROR(VLOOKUP($A40,[January numbers.xls]Sheet
1!$Y$4:$AI$500,5,FALSE)=TRUE)," ",(VLOOKUP($A40,[January numbers.xls]Sheet
1!$Y$4:$AI$500,5,FALSE)))

I have over a thousand formulas altogether and the file that they link to
change each month so I would like it to update based on what the current
month is. I hope this example would help.

Okay... still not a macro solution... what about just using the Replace All
button on Excel's Edit/Replace menu bar item (click the Options button to
expose the additional options and select Workbook from the "Within" drop
down)?

--
Rick (MVP - Excel)


"Chad" wrote in message
...


"Rick Rothstein" wrote:

Any chance you can change all formulas that contain a month name to use a
cell reference instead of the text month? For example, if you had a
formula
in your workbook like this...

=IF(B2<"","January's value is"&B2","")

and A1 was a currently unused cell, You could write January in A1 and
change
the formula to this...

=IF(B2<"",A1&"'s value is"&B2","")

If you made similar changes to all your formulas that specified the
month's
name in text form, then just changing A1 would automatically update all
the
formulas at once. This would be much faster than executing a macro that
looked at, and changed, each formula with the month name in text form. If
you can do this, fine. If not, then are there more than one worksheet in
your workbook?

--
Rick (MVP - Excel)


Do not think I can do that cause the text for the month is part of a file
name such as "January numbers.xls" EX. one formula is this
=IF(ISERROR(VLOOKUP($A40,[January numbers.xls]Sheet
1!$Y$4:$AI$500,5,FALSE)=TRUE)," ",(VLOOKUP($A40,[January numbers.xls]Sheet
1!$Y$4:$AI$500,5,FALSE)))

I have over a thousand formulas altogether and the file that they link to
change each month so I would like it to update based on what the current
month is. I hope this example would help.