CODEWITHSUNDEEP
excelexcel-formulavlookupexcel-2013
user2824889
user2824889

Reputation: 1185

Excel: Evaluating a string (obtained through vlookup) as a formula

I have a list of formulas, as strings, in a separate worksheet (tbl2), such as the following:

0.01 * INDIRECT("RC[1]", FALSE) * 0.01

In the working sheet, one cell has the following formula that references one of those strings:

=VLOOKUP(B22, tbl2!B4:J68, 2, FALSE)

That returns the string-form formula. How can I turn the string into an actual formula to evaluate its result?

Upvotes: 1

Views: 307

Answers (1)

J. Chomel
J. Chomel

Reputation: 8393

Here is what you could try using VBA sorry -- say your formula is in sh1:A1, and result will be in sh1:B1. In my case (Excel2010), it can valuate formulas ; I believe it is ok if input string in A1 comes from Vlookup:

Public Sub this()
    mySheet = "sh1"
    Dim C As Object
    Set myrange = ThisWorkbook.Sheets(mySheet).Range("A1:A1")
    For Each C In myrange
        C.Offset(0, 1).FormulaR1C1 = "=" & C.Value
        C.Offset(0, 1).Select
    Next
End Sub

In case your formula contains cells or ranges from a workbook, you can check that one I did.

Upvotes: 0

Related Questions