Macro Questions

  • Thread starter Thread starter Chad
  • Start date Start date
C

Chad

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 Rothstein said:
Yes to all three. If you supply actual details, someone here can show you
how to write the macros for them.
Click to expand...

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 Rothstein said:
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?
Click to expand...


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 said:
Rick Rothstein said:
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?
Click to expand...


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.
Click to expand...
 
Back
Top