CODEWITHSUNDEEP
vbaexcel
DPM
DPM

Reputation: 61

Set a range to a formula then to a value

I am looking for a simpler way of setting a cell in Excel to a formula, then a value. Below is a subset of a macro I have written.

Range("InsuredName").Value = "=INDEX(Data,RetrievalRow,InsuredNameIndex)"
Range("InsuredName").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

Can I accomplish this without having to copy, paste-special-values? I have an input sheet in a model where a user puts in necessary inputs and the model performs certain calculations based on those inputs. I have built in functionality where they can save and retrieve their inputs at a later time. So, upon demand to retrieve a specified set of inputs, this macro puts a formula in the appropriate cells on the input tab to perform look-ups in the data tab where the inputs are stored. Then, it pastes that cells to values. This is done repeatedly for a number of inputs. I assume there is an easier way, but I have not been able to figure it out.

Upvotes: 0

Views: 65

Answers (2)

SierraOscar
SierraOscar

Reputation: 17647

Use the Evaluate() method

Range("InsuredName").Value = Evaluate("INDEX(Data,RetrievalRow,InsuredNameIndex)")

If there are no VBA variables to be used, you can also use the shorthand for this which is square brackets:

Range("InsuredName").Value = [INDEX(Data,RetrievalRow,InsuredNameIndex)]

You can also evaluate a named range to return the range object - so again, you can use shorthand expression:

[InsuredName] = [INDEX(Data,RetrievalRow,InsuredNameIndex)]

Upvotes: 2

Excel Hero
Excel Hero

Reputation: 14764

Replace all with this:

With Range("InsuredName")
    .Value = "=INDEX(Data,RetrievalRow,InsuredNameIndex)"
    .Value = .Value
End With

Upvotes: 2

Related Questions