Easier way to record FormulaR1C1 entries from a worksheet?

[ronnie76]

Have a worksheet which contains about unique Excel formulas in over 1,500 different cells. Trying to create a macro which will recreate this worksheet whenever needed. The only way I know how to record all these entries is to double click into each cell then hit enter which then records the FormulaRIC1 entry. Is there an easier way to record all of the cell entries from the source sheet?

Would prefer not to copy and paste if possible, but if that is the best way, then I would need a way not to copy absolute workbook references. Example below.

Copying from one workbook to another this

=IF(ISBLANK($D13),"",VLOOKUP($D13,'Raw_Material_Data'!$A:$AY,6,FALSE))

becomes this

=IF(ISBLANK($D13),"",VLOOKUP($D13,'[Formulation Template v3.3 with Create Macro.xlsm]Raw_Material_Data'!$A:$AY,6,FALSE))

Which causes the new workbook to try and reference the source workbook when opening.

Thanks so much for any help or advice.

 

[ekrause]

Can you copy and paste formulas then use Find and Replace (CTRL + h) and replace "[Formulation Template v3.3 with Create Macro.xlsm]" with ""?

That will prevent the formula from referencing an outside worksheet. You'll need to be careful that you have the correct tab names though or you may get some nasty errors.

 

[offthelip]

Try this code, the way it works is it loads the formulae in the active sheet into a variant array and then loops through each cell. When it finds a formula it generates the VBA code to put the formula into the same cell
When it has completed the loop it adds and extra worksheet and writes the code out into column A. Now select all the rows with code in it in column A, and select copy, then open a new subroutine in VBA with no code in it an paste the code into the subroutine. This will replicate the equations in your worksheet exactly as they were.

Sub form()
Dim outarr() As Variant
tt = Chr(34)
formarr = ActiveSheet.UsedRange.Formula
maxdim = UBound(formarr, 1) * UBound(formarr, 2)
ReDim outarr(1 To maxdim, 1 To 1)
indi = 1
For i = 1 To UBound(formarr, 1)
For j = 1 To UBound(formarr, 2)
   If formarr(i, j) <> "" Then
    outarr(indi, 1) = "cells(" & i & "," & j & ").formula=" & tt & formarr(i, j) & tt
    indi = indi + 1
   End If
Next j
Next i
ActiveWorkbook.Sheets.Add
Range(Cells(1, 1), Cells(indi, 1)) = outarr


End Sub

 

[ronnie76]

Can you copy and paste formulas then use Find and Replace (CTRL + h) and replace "[Formulation Template v3.3 with Create Macro.xlsm]" with ""?

That will prevent the formula from referencing an outside worksheet. You'll need to be careful that you have the correct tab names though or you may get some nasty errors.

Thanks so much for the response. Yes, that does work, though it does create significant chance for errors as you pointed out.

 

[ronnie76]

Try this code, the way it works is it loads the formulae in the active sheet into a variant array and then loops through each cell. When it finds a formula it generates the VBA code to put the formula into the same cell
When it has completed the loop it adds and extra worksheet and writes the code out into column A. Now select all the rows with code in it in column A, and select copy, then open a new subroutine in VBA with no code in it an paste the code into the subroutine. This will replicate the equations in your worksheet exactly as they were.

Sub form()
Dim outarr() As Variant
tt = Chr(34)
formarr = ActiveSheet.UsedRange.Formula
maxdim = UBound(formarr, 1) * UBound(formarr, 2)
ReDim outarr(1 To maxdim, 1 To 1)
indi = 1
For i = 1 To UBound(formarr, 1)
For j = 1 To UBound(formarr, 2)
   If formarr(i, j) <> "" Then
    outarr(indi, 1) = "cells(" & i & "," & j & ").formula=" & tt & formarr(i, j) & tt
    indi = indi + 1
   End If
Next j
Next i
ActiveWorkbook.Sheets.Add
Range(Cells(1, 1), Cells(indi, 1)) = outarr


End Sub

Outstanding! Thank you so much for the reply and help!

 

[ronnie76]

Try this code, the way it works is it loads the formulae in the active sheet into a variant array and then loops through each cell. When it finds a formula it generates the VBA code to put the formula into the same cell
When it has completed the loop it adds and extra worksheet and writes the code out into column A. Now select all the rows with code in it in column A, and select copy, then open a new subroutine in VBA with no code in it an paste the code into the subroutine. This will replicate the equations in your worksheet exactly as they were.

Sub form()
Dim outarr() As Variant
tt = Chr(34)
formarr = ActiveSheet.UsedRange.Formula
maxdim = UBound(formarr, 1) * UBound(formarr, 2)
ReDim outarr(1 To maxdim, 1 To 1)
indi = 1
For i = 1 To UBound(formarr, 1)
For j = 1 To UBound(formarr, 2)
   If formarr(i, j) <> "" Then
    outarr(indi, 1) = "cells(" & i & "," & j & ").formula=" & tt & formarr(i, j) & tt
    indi = indi + 1
   End If
Next j
Next i
ActiveWorkbook.Sheets.Add
Range(Cells(1, 1), Cells(indi, 1)) = outarr


End Sub

@offthelip

Thank you so much. The code worked great, but there were just a few errors. Do you happen to know of a way to avoid the compile errors like the ones listed below?


Recording the macro result

Range("B3").Select
    ActiveCell.FormulaR1C1 = _
        "=REPLACE( CELL(""filename"",R[-2]C[-1]),1, FIND(""]"",CELL(""filename"",R[-2]C[-1])),"""")"

Your extraction code result which results in a Compile Error on "filename"

cells(3,2).formula="=REPLACE( CELL("filename",A1),1, FIND("]",CELL("filename",A1)),"")"

Recording the macro result

ActiveCell.FormulaR1C1 = _
        "=IF(RC[-1]="""","""",IF(RC[-1]=""Natural Flavors"",SUMIF(R93C12:R132C12,RC[-1],R93C13:R132C13)-R133C13,SUMIF(R93C12:R132C12,RC[-1],R93C13:R132C13)))"

Your extraction code result which results in a Compile Error on "Natural"

cells(97,8).formula="=IF(G97="","",IF(G97="Natural Flavors",SUMIF($L$93:$L$132,G97,$M$93:$M$132)-$M$133,SUMIF($L$93:$L$132,G97,$M$93:$M$132)))"

 

[ronnie76]

Found what I think is the proper find and replace sequence which will fix the errors, so I can just use that if necessary.

On the ones with the compile errors it would be replace

" with ""
""= with "=
)"" with )"

 

[offthelip]

Sorry ,I forgot to take that into account, it is one of the the reasons I use the constant "tt=chr(34) which is a " . It makes is a bit clearer which double quote is part of the VBA and which is part of the text.
However you hopefully have found the solution.