partially replace formula problem. Please help!

[Guest]

I have a whole bunch of formula with some parts similar like
“MVJan!$A$13:$Z$68†in about 12 worksheets, but Feb will change
MVFeb!$A$13:$Z$68 and so on.

Now I want these similar formula to be placed as
“INDIRECT("MV"&mth&"!$A$8:$Z$18")†mth is defined name to define the cell
that I can change the month and reference area will be changed.

The problem now is that reference area is not always the same. $A$13:$Z$68
can be $A$35:$Z$177, etc.


How can I replace this kind of formula in a batch? What’s kind of code
should I use? Can I use object?


Your help is really appreciated!


Thanks

 

[Guest]

You could probably use Find and Replace to do that, using the "look in
formulas" option.
I don't know how many sheets are involved or the ranges containing the
formulas, but you could write a macro to go through all worksheets in the
workbook, examine the .Formula value of each (appropriate) cell on them and
change the .Formula property of the ones you need to in the macro. Something
that looks a little like this:

Sub ChangeFormulas()
Dim anySheet as Worksheet
dim anyCell as Object
For Each anySheet in Worksheets
For Each anyCell in anySheet.Cells
If Left(anyCell.Formula, 7) = "=MVJan!" Then
..... and in here is where you would build a new formula
..... splitting up the old one to preserve the cell references and
..... and rebuilding it as your =INDIRECT(....) formula
End If
Next
Next
End Sub

by looking at anySheet.Cells, it could take a while to run, so if you can
refine that to a specific range of cells, it would certainly go faster.