Evaluate function with R1C1 formulas

**eiem** · 01-16-2010, 04:33 AM

Hi folk,
first of all, as I’m new in the forum, I’d like to greet everybody and thank in advance for any help

Shortly my question:
I need to fill a cells range with the RESULT of a formula (NOT the formula itself!)
If I use :

Range("C1:D3").FormulaR1C1  =  "=RC[-2]+RC[-1]"

It works, but it inputs the formula in each cell, therefore if I apply a long formula to 5000+ cells the worksheet become HUGE!
If I use :

Range("C1:D3").FormulaR1C1  =  Evaluate("=RC[-2]+RC[-1]")

Simply it doesn’t work! (#VALORE!)
Any chance I can reach the scope?

Thanks guys

Alex

**DonkeyOte** · 01-16-2010, 04:48 AM

With temp formulae and subsequent overwrite:

With Range("C1:D3")
    .FormulaR1C1  =  "=RC[-2]+RC[-1]"
    .Value = .Value
End With

Using Evaluate will be tricky I think, at least in one iteration, given the fact D will SUM based on B+C and C itself is not calculated until the Evaluate is run - you would thus need to iterate the Evaluate twice over (I think).

Incidentally... Evaluate would work like so:

Dim bLoop As Byte
For bLoop = 1 To 2 Step 1
    With Range("C1:D3")
        .Value = Evaluate(.Offset(,-2).Address & "+" & .Offset(,-1).Address)
    End With
Next bLoop

**eiem** · 01-16-2010, 05:45 AM

GREAT!!!

Thank you very much, it does work perfectly!

I’ll use the “with” function which fits my scope 100%; in the example I gave the iteration was accidental and in my case I don’t have any iteration so it works perfectly.

In any case I’ll take in mind also the second solution despite the offset operations are les human friendly to me :P.

Thank you and regards.

Alex