Forum Discussion
Save / Restore all formulas on a worksheet
The macro writes one formula in 10000 cells in this example. In cell I2 the formula =B2*C2 is entered, in cell I3 formula =B3*C3 and so on. If any changes are made in column I the formulas can be re-entered by running the macro. I wanted to show that the original formulas can be re-entered in almost no time with the help of a macro.
Can you share the formulas of your sheet? Maybe the formulas can be written into the file with the R1C1 code.
Sub test()
Dim ws As Worksheet
Dim lastRow As Long
Dim str1 As Variant
Dim str2 As Variant
Range("I:I").Clear
Set ws = Tabelle1
lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).EntireRow.Row
str1 = ws.Range("B2:B" & lastRow).Value
str2 = ws.Range("C2:C" & lastRow).Value
ws.Range("I2:I" & lastRow).FormulaR1C1 = "=RC[-7]* RC[-6]"
End SubMaybe this is a helpful example. In the attached file you can click the button in cell E2 to run the macro. The macro enters a R1C1 formula in column I and the runtime is less than 1 second for 10000 rows in my sheet.
I am not clear on what that macro is doing, could you explain?
Also, here is a screenshot of my worksheet, every cell visible starts with a formula in it, until the user overwrites it with changes.
Edit: OK, i see, your writing one formula to one cell correct?
unfortunately i am dealing with hundreds of cells (in the image above, this thing extends to the right for quite a ways) and the formulas are different in each cell.
I was hoping for an automated way to copy all the formulas and store them to be reapplied later with a macro.
The macro writes one formula in 10000 cells in this example. In cell I2 the formula =B2*C2 is entered, in cell I3 formula =B3*C3 and so on. If any changes are made in column I the formulas can be re-entered by running the macro. I wanted to show that the original formulas can be re-entered in almost no time with the help of a macro.
Can you share the formulas of your sheet? Maybe the formulas can be written into the file with the R1C1 code.
- I need to restore my data on in My " C" column of my worksheet ..how do I do that ?
