CODEWITHSUNDEEP
excelvbaexcel-formula
John Bustos
John Bustos

Reputation: 19574

Excel VBA - Convert a string Formula to an Excel Calculation in a range of cells

I'm just wondering if this is possible to do without a loop - In my excel sheet, in, say, Range("A1:C10") I have text concatenation formulas that, once concatenated, create real Excel functions.

As a stupid example, suppose I had the following in cell A1:

A1: ="=Sum(D"&C2&":E"&C3&")"

Now, I know in VBA I can do something along the following for any one specific cell:

Range("A1").Formula = Range("A1").Text

And it will convert my text formula into an Excel formula and evaluate it.

Now, what I'm curious about is, whether there a way to say, for example:

Range("A1:C10").Formula = Range("A1:C10").Text

Without looping through each cell individually?

Also, I can't use INDIRECT() as, unfortunately, my formulas refer to closed workbooks :/

Any ideas??

Upvotes: 0

Views: 2642

Answers (2)

Mathieu Guindon
Mathieu Guindon

Reputation: 71227

Range.Text contains the string representation of the cell's value. The actual calculated value (which I suspect is what you're after) is accessed using Range.Value - try this:

Range("A1:C10").Formula = Range("A1:C10").Value

Upvotes: 1

Slai
Slai

Reputation: 22886

Not sure if this is what you are trying to do, but if for example you use:

Range("A1:C10").Formula = "=Sum(D1:E1)"

then the relative references will be auto adjusted:

A1: =Sum(D1:E1)
A2: =Sum(D2:E2)
B1: =Sum(E1:F1)
... etc.

Upvotes: 0

Related Questions