If I copy "EXAMPLE FORMULA" below from a cell in mySheet2 and paste special
only the formula into the same cell in mySheet3, obviously the cell in
mySheet3 will retain the same cell references used in the mySheet2 copied
formula.

My problem with this is after pasting EXAMPLE FORMULA from mySheet2 to
mySheet3, I need the mySheet3 formula to look like EXAMPLE RESULTS. My
question is there a way using vba to iterate through a spreadsheet and
modify cell references?

For example, on mySheet3, after copy pasting special the formulas, I'd loop
through all cells changing any reference to mySheet2 to be mySheet3. Then
I'd change all formulas using mySheet1 to use mySheet2.

Can this be done with code or any other way?


EXAMPLE FORMULA:

=IF('mySheet1'!B5="","",'mySheet1'!B5+'mySheet2'!F5)


EXAMPLE RESULTS:

=IF('mySheet1'!B5="","",'mySheet2'!B5+'mySheet3'!F5)

turn on the macro recorder and do

Edit=>Replace

What: String to be Replace
With: String to use as a replacement

Turn off the macro recorder and use the recorded code. Make sure you have
selected the option to do replacements on less than the whole formula in the
cell.


Worksheets("Sheet1").Cells.Replace _
What:="Sheet2", _
Replacement:="Sheet1", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
--
Regards,
Tom Ogilvy

regards,
Tom Ogilvy


"scott" wrote:

> If I copy "EXAMPLE FORMULA" below from a cell in mySheet2 and paste special
> only the formula into the same cell in mySheet3, obviously the cell in
> mySheet3 will retain the same cell references used in the mySheet2 copied
> formula.
>
> My problem with this is after pasting EXAMPLE FORMULA from mySheet2 to
> mySheet3, I need the mySheet3 formula to look like EXAMPLE RESULTS. My
> question is there a way using vba to iterate through a spreadsheet and
> modify cell references?
>
> For example, on mySheet3, after copy pasting special the formulas, I'd loop
> through all cells changing any reference to mySheet2 to be mySheet3. Then
> I'd change all formulas using mySheet1 to use mySheet2.
>
> Can this be done with code or any other way?
>
>
> EXAMPLE FORMULA:
>
> =IF('mySheet1'!B5="","",'mySheet1'!B5+'mySheet2'!F5)
>
>
> EXAMPLE RESULTS:
>
> =IF('mySheet1'!B5="","",'mySheet2'!B5+'mySheet3'!F5)
>
>
>