Try something like this:
In this example the formula are in Sheet1 cells B2:C2 & C6:D6.
Select range B2:D6 then run "Sub export_formula"
Book1 |
---|
|
---|
| A | B | C | D |
---|
1 | N | | | |
---|
2 | U | NU | NU | |
---|
3 | D | | | |
---|
4 | W | Ryan | Ronald | Donna |
---|
5 | B | | | |
---|
6 | G | | BG | QS |
---|
7 | Q | | | |
---|
8 | S | | | |
---|
|
---|
The result in sheet Helper is like this (note: sheet Helper must be a clean sheet):
Book1 |
---|
|
---|
| A | B | C | D |
---|
1 | | | | |
---|
2 | | A1&A2 | B1&B2 | |
---|
3 | | | | |
---|
4 | | | | |
---|
5 | | | | |
---|
6 | | | A5&A6 | A7&A8 |
---|
7 | | | | |
---|
|
---|
To send the formulas back to Sheet1:
Select the range in sheet Helper
Run Sub import_formula
If the formulas are in a fixed range then you can replace "Selection" in the macro with the range, so you don't need to select the the range before running the macro.
VBA Code:
Sub export_formula()
Dim c As Range
Application.ScreenUpdating = False
Application.EnableEvents = False
For Each c In Selection.SpecialCells(xlCellTypeFormulas)
Sheets("Helper").Range(c.Address) = Mid(c.Formula, 2)
Next
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Sub import_formula()
Dim c As Range
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlManual
For Each c In Selection.SpecialCells(xlCellTypeConstants)
With Sheets("Sheet1")
If Not .Range(c.Address).HasFormula Then
.Range(c.Address) = "=" & c
End If
End With
Next
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlAutomatic
End Sub