0

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??

John Bustos
  • 19,036
  • 17
  • 89
  • 151

2 Answers2

1

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
Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
0

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.
Slai
  • 22,144
  • 5
  • 45
  • 53
  • Sadly, this is not what I'm looking for - My formulas are very complex, that was just a simple example to show what I meant by the string concatenation... @MatsMug got the correct answer. But thanks for your input!! – John Bustos Sep 14 '16 at 17:29