CODEWITHSUNDEEP
excelvariablesexcel-formulaexcel-2010vba
D.Mascari
D.Mascari

Reputation: 3

Declare Formula as Variable in vba 2010

I have been racking my brain for 2 days on why I can't get this to work so after years of searching threads, I'm posting my first question.

I have formulas stored in a table in a workbook. The table is named Adjustments. On another worksheet in the same workbook, I'm looking up the formulas based on loan numbers. Before I copy the worksheet and save it as a separate document I want to replace the lookup formula with the formula it looks up and leave as an active formula instead of just the value it evaluates to.

The lookup formula currently in cell E6 is: =(INDEX(Adjustments,MATCH(Loan_No,Adjustments[Loan No],0),COLUMN(Adjustments[GPR Adj])))

The formula that is referencing is:

=IF(AND(COM_GPR_ACT<>0,OR([@[Vacancy Assumption]]+COM_OCC_ACT<=100%,IFERROR(((COM_VAC_RPTD/COM_GPR_RPTD)>[@[Vacancy Assumption]]),FALSE))),0,(COM_RENTS_ACT*COM_NRSF-COM_GPR_ACT))

I want to replace the Index/Match formula with the formula in the cell that it is looking up.

Here is the code that I was trying to use.

Dim GPR As String
GPR = [INDEX(Adjustments,MATCH(Loan_No,Adjustments[Loan No],0),COLUMN(Adjustments[GPR Adj]))].Formula
Range("E6").Formula = GPR

When I use this I get a Run-Time Error '1004': Application-defined or object defined error.

I have tried changing the evaluate application to .Value but that gives me the final value of both formulas. It also seems that if I had Excel 2013 I could use [FormulaText(GPR)].

I am trying to avoid doing a copy and pasteformulas because there are about 25 cells that I want to replace and I don't want to slow down the macro since this is only one of several subs that run. It seems like it should be a simple answer, but I have yet to stumble upon it. Any help is appreciated!

Upvotes: 0

Views: 1947

Answers (2)

Charles Williams
Charles Williams

Reputation: 23505

This should work if it is a valid formula in cell E6, so I suspect there is a problem with the formula.

Probably the error is that you are using unqualified structured table references like [@[Vacancy Instruction]] - try prefixing them with the table name.

Upvotes: 2

S Meaden
S Meaden

Reputation: 8260

Ok, quite funky requirement there. One approach is to do the following.

  1. Go to Menu and take File->Options, then select the Formulas tab.
  2. Check the checkbox for R1C1 reference style and click OK to dimiss dialog.
  3. On the sheet select Name Manager from the Formula ribbon.
  4. In the name manager define a name called "ToMyLeft" and in the Refers to box enter =Sheet1!RC[-1]

Now you have defined a relative name which always points to a cell to the left of the one in which is is used! Quit funky. Take some time for that to sink in.

Now you can write formulae like this

=ADDRESS(ROW(ToMyLeft),COLUMN(ToMyLeft))

and this will give the address of the cell to the left which is a cell address. So this returns $F$6 when executed in the cell F7. This should give you a hook to the ultimate formula's home. You will still need some VBA function to execute it though such like.

Option Explicit

Function FunkyIndirectFunctionEval(ByVal sCellAddress As String)

    Dim ws As Excel.Worksheet
    Set ws = ActiveSheet '<--- if you workbook spans several sheets you'll need to handle the sheets, second param?


    Dim rng As Excel.Range
    Set rng = ws.Range(sCellAddress)

    Dim vEval As Variant
    FunkyIndirectFunctionEval = Application.Evaluate(rng.Formula)

End Function

But this will have formula context problems because one is stripping out the formula from its context. I have not tried this, I'd say chance of success 25%!

Don't forget to change back the RC reference to A1 reference in the Options dialog.

Upvotes: 1

Related Questions