Worksheet.Evaluate to Formulae

[D_Marcel]

Gurus,
Greetings to all of you.
I'm creating a report to analyze the competitiveness of the company that I'm working for, regarding the concorrence. Several data sources are required and to join all the data in a single managerial summary, dozens of formulae is required, as well as hundreds of VBA lines.

I'm trying to store all the formulae in a table, get them through a loop, store into a multidimensional Array, and then use the Worksheet.Evaluate method to unload the formula to the ranges, which varies from column to column:

[VBA]CONSOL_WS.Activate LAST_ROW = Cells(Rows.Count, 1).End(xlUp).Row
With CONSOL_WS
For AINDEX = LBound(FORMULAE_ARRAY) To UBound(FORMULAE_ARRAY)
FIELD_NAME = FORMULAE_ARRAY(AINDEX, 2)


.Range(Cells(2, Range("Competitividade_DB[" & FIELD_NAME & "]").Column), _
Cells(LAST_ROW, Range("Competitividade_DB[" & FIELD_NAME & "]").Column)).FormulaR1C1 = _
CONSOL_WS.Evaluate(FORMULAE_ARRAY(AINDEX, 5))

.Range(Cells(3, Range("Competitividade_DB[" & FIELD_NAME & "]").Column), _
Cells(LAST_ROW, Range("Competitividade_DB[" & FIELD_NAME & "]").Column)).Copy

.Range(Cells(3, Range("Competitividade_DB[" & FIELD_NAME & "]").Column), _
Cells(LAST_ROW, Range("Competitividade_DB[" & FIELD_NAME & "]").Column)).PasteSpecial _
xlPasteValues, xlNone, False, False
Next AINDEX
End With[/VBA]

This instruction:

[VBA]Range("Competitividade_DB[" & FIELD_NAME & "]").Column[/VBA]

Means that I'm inserting the formulae in the columns of a Table Object, so I can store also the names of the fields in that array and select them dinamically in the loop.

Otherwise, I would have to write dozens of lines like this one:

[VBA]COL_NUMBER = Range("Competitividade_DB[CUSTO]").Column
Set RNG = Range(Cells(2, COL_NUMBER), Cells(LAST_ROW, COL_NUMBER))
RNG.FormulaR1C1 = _
"=IF([@PRECO]=""NULL"",""NULL"",SUM(Competitividade_DB[@[PRECO]:[FRETE]]))"
Set RNG = Range(Cells(3, COL_NUMBER), Cells(LAST_ROW, COL_NUMBER))
RNG.Copy
RNG.PasteSpecial xlPasteValues, xlNone, False, False[/VBA]

The problem is that the Worksheet.Evaluate isn't working for me, it's returning the "Error 2019" (#NAME).
The formulae strings that I'm using in the .Evaluate method matchs exactly the corresponding formula instruction on VBA:

"=IF([@PRECO]=""NULL"",""NULL"",SUM(Competitividade_DB[@[PRECO]:[FRETE]]))"


What can I be doing wrong?

[Aflatoon]

It looks as though you are trying to put the actual formula into the cells so you shouldn't be using Evaluate at all.

[D_Marcel]

Aflatoon,
Really dude, I'm feeling so dumb now, you have no idea. I did not even think about insert the formulae directly into the cells without having to use .Evaluate.
I did. Worked perfectly:

[VBA].Range(Cells(2, Range("Competitividade_DB[" & FIELD_NAME & "]").Column), _Cells(LAST_ROW, Range("Competitividade_DB[" & FIELD_NAME & "]").Column)).Value = _
FORMULAE(AINDEX, 5)[/VBA]

Thanks!