D_Marcel
12-07-2017, 10:16 AM
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:
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
This instruction:
Range("Competitividade_DB[" & FIELD_NAME & "]").Column
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:
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
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?
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:
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
This instruction:
Range("Competitividade_DB[" & FIELD_NAME & "]").Column
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:
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
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?