Excel Cell Copy - Update Formulas
-
Hi, I'm trying to copy from lets say column "AC" to column "AA". The cell contains a formula which I have gotten and built an array out of the spreadsheet. Where I am stuck is how do you get the formula to update. IE: If the formula was =ROUND(AC25) when I copy it programatically to column AA, it still says =ROUND(AC25) instead of =ROUND(AA25). I know I can use the replace command and do a string replacement, however if the formula is: =ROUND(A5+AB5) and is in column C, when I try to replace the A to a C, it becomes =ROUND(C5+CB5) instead of =ROUND(C5+AB5). So my question is there a built in method into the Excel that I can call to perform the cell copy updating the formulas such as Excel does when you do it by hand? Thank you,
Glenn
-
Hi, I'm trying to copy from lets say column "AC" to column "AA". The cell contains a formula which I have gotten and built an array out of the spreadsheet. Where I am stuck is how do you get the formula to update. IE: If the formula was =ROUND(AC25) when I copy it programatically to column AA, it still says =ROUND(AC25) instead of =ROUND(AA25). I know I can use the replace command and do a string replacement, however if the formula is: =ROUND(A5+AB5) and is in column C, when I try to replace the A to a C, it becomes =ROUND(C5+CB5) instead of =ROUND(C5+AB5). So my question is there a built in method into the Excel that I can call to perform the cell copy updating the formulas such as Excel does when you do it by hand? Thank you,
Glenn
Hello Glenn, Not sure if it will help, but most of the Excel problems I had, I solved by recording a macro while doing it in excel, and then just transalte/adapt VBA to C#/Excel interop. Worked like a charm. It should work in your case too. :)
All the best, Dan