Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Formula References
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'!F 5) EXAMPLE RESULTS: =IF('mySheet1'!B5="","",'mySheet2'!B5+'mySheet3'!F 5) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Formula References
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'!F 5) EXAMPLE RESULTS: =IF('mySheet1'!B5="","",'mySheet2'!B5+'mySheet3'!F 5) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
changing multiple column references in a formula | Excel Worksheet Functions | |||
changing cell references | Excel Worksheet Functions | |||
Formula automatically changing cell references | Excel Worksheet Functions | |||
Changing many references at a time. | Excel Discussion (Misc queries) | |||
Changing Formula References in Visual Basic | Excel Programming |