Formulas : An Excel formula to extract the contents of

digita · Apr 14th 2004

Text

I am looking for a formula to retrieve the contents of a formula in another workbook. For example, if cell A1 in workbook named A.xls contains a formula =2+2. Can anyone think of a formula to extract the whole formula from this workbook instead of the end result, ie 4?

Your input will be appreciated.

Anonymous · Apr 14th 2004

I think you may have checked "Solved" by accident. If you have actually solved this problem, please post your solution. I'd love to see it.

If not solved, just UNCHECK "Solved" -- as thread author only you (or an OzGrid Admin) -- can do this. Seeing the "Solved" label on the thread will cause folks to ignore it.

If you are willing to use VBA, then the following shows how to get the formula from cell A1 of Sheet1 of workbook "Book2" and write it into cell B3 of the active worksheet so that it displays as a formula (text) instead of a result.

Code

Sub test()
Dim Form As String
Form = Workbooks("Book2").Sheets("Sheet1").Range("A1").Formula
Range("B3") = "'" & Form
End Sub

digita · Apr 15th 2004

Thank you for the reply by Tom who has provided a useful VBA code to extract the formula (not a result) from a cell in an external workbook. Now if I have about more than 250 cells in a column containing formulas that need to be extracted. I've tried to modify the code to the following & included an array to complete the task but had no success.

Option Explicit
Sub test()
form As Range
form = Workbooks("Book2").Sheets("Sheet1").Range("E2:E4").Formula
Range("B3").FormulaArray = form
End Sub

Is there a way to extract them all in one hit? Can someone help please?

Thank you for your input.

Digita

Will Riley · Apr 15th 2004

Do you want them extracted into separate cells in the other workbook...

i.e. the formulas in book1 A1:A250 get copied into Book2 A1:A250 as formulas as opposed to values ?

If that's the case, something like this clunky Loop would suffice,

Code

Option Explicit


Sub copyformula()
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim rngcell As Range
Dim rngformula As Range
Dim rngpaste As Range
Dim strformula As String


Set wb1 = Workbooks("Book1.xls")
Set wb2 = ActiveWorkbook
Set rngformula = wb1.Sheets(1).Range("A1:A250")


For Each rngcell In rngformula.Cells
    Set rngpaste = wb2.Sheets(1).Range("A65536").End(xlUp)(2)
    strformula = rngcell.Formula
    rngpaste = rngcell.Address & " contains " & strformula
Next rngcell


Set wb1 = Nothing
Set wb2 = Nothing
Set rngformula = Nothing
End Sub

Display More

Hope this helps,

Will

Formulas : An Excel formula to extract the contents of

Participate now!

Share