VBA optimisation formules qui prennent du tps Fermé

Question

Bonjour,

J'ai créé une macro, mais elle prend pas mal de temps à cause des formules. 

Voilà ce que j'ai fait à ce niveau:

Wbk2.Sheets("DataPrimeTerminal").Activate
For i = 7 To 65000
If Cells(i, 1) <> "" Then
Cells(i, 2).FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC1,DataGPMS!R7C4:R154C24,2,FALSE)),"" "",(VLOOKUP(RC1,DataGPMS!R7C4:R154C24,2,FALSE)))"
Cells(i, 3).FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC1,DataGPMS!R7C4:R154C24,4,FALSE)),"" "",(VLOOKUP(RC1,DataGPMS!R7C4:R154C24,4,FALSE)))"
Cells(i, 4).FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC1,DataGPMS!R7C4:R154C24,6,FALSE)/1000),"" "",(VLOOKUP(RC1,DataGPMS!R7C4:R154C24,6,FALSE)/1000))"
Cells(i, 5).FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC1,DataGPMS!R2C4:R149C21,14,FALSE)),"" "",(VLOOKUP(RC1,DataGPMS!R2C4:R149C21,14,FALSE)))"
Cells(i, 6).FormulaR1C1 = "=IF(ISERROR(RTD(""MdgServer"","""",""AUTO"",RC5,""NAME_SECURITY"")),"" "",(RTD(""MdgServer"","""",""AUTO"",RC5,""NAME_SECURITY"")))"
Cells(i, 7).FormulaR1C1 = "=IF(ISERROR(RTD(""MdgServer"","""",""AUTO"",RC5,""ID_CURRENCY"")),"" "",(RTD(""MdgServer"","""",""AUTO"",RC5,""ID_CURRENCY"")))"
Cells(i, 8).FormulaR1C1 = "=IF(ISERROR(RTD(""MdgServer"","""",""AUTO"",RC5,""ISIN"")),"" "",(RTD(""MdgServer"","""",""AUTO"",RC5,""ISIN"")))"
Cells(i, 9).FormulaR1C1 = "=IF(ISERROR(RTD(""MdgServer"","""",""AUTO"",RC5,""DATE_TODAY"")),"" "",(RTD(""MdgServer"","""",""AUTO"",RC5,""DATE_TODAY"")))"
Cells(i, 10).FormulaR1C1 = "=IF(ISERROR(VALUE(RTD(""MdgServer"","""",""AUTO"",RC5,""DATE_TIME""))),"" "",(VALUE(RTD(""MdgServer"","""",""AUTO"",RC5,""DATE_TIME""))))"
Cells(i, 11).FormulaR1C1 = "=IF(ISERROR(VALUE(RTD(""MdgServer"","""",""AUTO"",RC5,""PREV_PRICE""))),"" "",(VALUE(RTD(""MdgServer"","""",""AUTO"",RC5,""PREV_PRICE""))))"
Cells(i, 12).FormulaR1C1 = "=IF(ISERROR(VALUE(RTD(""MdgServer"","""",""AUTO"",RC5,""LAST_PRICE_WO_ADDENDUM""))),"" "",(VALUE(RTD(""MdgServer"","""",""AUTO"",RC5,""LAST_PRICE_WO_ADDENDUM""))))"
Cells(i, 13).FormulaR1C1 = "=IF(ISERROR(VALUE(RTD(""MdgServer"","""",""AUTO"",RC5,""CN_DIFF_REL""))),"" "",(VALUE(RTD(""MdgServer"","""",""AUTO"",RC5,""CN_DIFF_REL""))))"
Cells(i, 14).FormulaR1C1 = "=IF(ISERROR(VALUE(RTD(""MdgServer"","""",""AUTO"",RC5,""TODAY_VOLUME""))),"" "",(VALUE(RTD(""MdgServer"","""",""AUTO"",RC5,""TODAY_VOLUME""))))"
Cells(i, 15).FormulaR1C1 = "=IF(ISERROR(VALUE(RTD(""MdgServer"","""",""AUTO"",RC5,""VWAP_PRICE""))),"" "",(VALUE(RTD(""MdgServer"","""",""AUTO"",RC5,""VWAP_PRICE""))))"
Cells(i, 16).FormulaR1C1 = "=IF(ISERROR(VALUE(RTD(""MdgServer"","""",""AUTO"",RC5,""CN_DIFF_REL""))),"" "",(VALUE(RTD(""MdgServer"","""",""AUTO"",RC5,""CN_DIFF_REL""))))"
Cells(i, 17).FormulaR1C1 = "=IF(ISERROR(VALUE(RTD(""MdgServer"","""",""AUTO"",RC5,""CN_DIFF_REL_W1""))),"" "",(VALUE(RTD(""MdgServer"","""",""AUTO"",RC5,""CN_DIFF_REL_W1""))))"
Cells(i, 18).FormulaR1C1 = "=IF(ISERROR(VALUE(RTD(""MdgServer"","""",""AUTO"",RC5,""CN_DIFF_REL_W4""))),"" "",(VALUE(RTD(""MdgServer"","""",""AUTO"",RC5,""CN_DIFF_REL_W4""))))"
Cells(i, 19).FormulaR1C1 = "=IF(ISERROR(VALUE(RTD(""MdgServer"","""",""AUTO"",RC5,""CN_DIFF_REL_M3""))),"" "",(VALUE(RTD(""MdgServer"","""",""AUTO"",RC5,""CN_DIFF_REL_M3""))))"
Cells(i, 20).FormulaR1C1 = "=IF(ISERROR(VALUE(RTD(""MdgServer"","""",""AUTO"",RC5,""CN_DIFF_REL_W52""))),"" "",(VALUE(RTD(""MdgServer"","""",""AUTO"",RC5,""CN_DIFF_REL_W52""))))"
Cells(i, 21).FormulaR1C1 = "=IF(ISERROR(VALUE(RTD(""MdgServer"","""",""AUTO"",RC5,""CN_DIFF_REL_CM""))),"" "",(VALUE(RTD(""MdgServer"","""",""AUTO"",RC5,""CN_DIFF_REL_CM""))))"
Cells(i, 22).FormulaR1C1 = "=IF(ISERROR(VALUE(RTD(""MdgServer"","""",""AUTO"",RC5,""CN_DIFF_REL_CY""))),"" "",(VALUE(RTD(""MdgServer"","""",""AUTO"",RC5,""CN_DIFF_REL_CY""))))"
Cells(i, 23).FormulaR1C1 = "=IF(ISERROR(VALUE(RTD(""MdgServer"","""",""AUTO"",RC5,""CN_MARKET_CAP_REDOM""))/1000000/RC[2]),"" "",(VALUE(RTD(""MdgServer"","""",""AUTO"",RC5,""CN_MARKET_CAP_REDOM""))/1000000/RC[2]))"
Cells(i, 24).FormulaR1C1 = "=+IF(Performance!R3C9=""EUR"",VLOOKUP(DataPrimeTerminal!RC[-22],Markets!R5C2:R100C4,2,FALSE),VLOOKUP(DataPrimeTerminal!RC[-22],Markets!R5C2:R100C4,3,FALSE))"
Cells(i, 25).FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-18],Markets!R4C1:R58C6,6,FALSE)),"" "",(VLOOKUP(RC[-18],Markets!R4C1:R58C6,6,FALSE)))"
Cells(i, 26).FormulaR1C1 = "=IF(ISERROR((RC[-23]*RC[-14]/RC[-2])/RC[-1]/1000),RC[-22],(RC[-23]*RC[-14]/RC[-2])/RC[-1]/1000)"
Cells(i, 27).FormulaR1C1 = "=IF(ISERROR(RC[-4]/RC[-3]),"" "",(RC[-4]/RC[-3]))"
Cells(i, 28).FormulaR1C1 = "=IF(ISERROR((RC[-5]/RC[-17])*1000*RC[-3]),"" "",(RC[-5]/RC[-17])*1000*RC[-3])"
Else
Exit For
End If

Existe-il un moyen d'optimiser celà?

Merci d'avance!

lermite222 · Answer

Bonjour,
Déjà ceci, tu a réellement 65000 lignes ??
tu peu remplacer
For i = 7 To 65000
par
For i = 7 To range("A65536").End(xlup).Row
Ca éviterra de passer les 65000 lignes si elles ne sont pas remplie !
Pour tes formules ! beh oui, elles sont compliquées.
A+

Jon · Answer

Merci pour ta réponse! Je vais faire ça tout de suite. 

En fait j'aurais surtout voulu savoir s'il n'était pas possible d'utiliser une autre méthode pour généraliser les formules pour chaque ligne. 
Est-il par exemple possible de proécder à un autofill (que je ne sais pas faire en vba) et si oui, cela me fera-t-il gagner du temps?

Merci encore!

lermite222 · Answer

Désolé mais je ne suis pas asser fort en fonction Excel pour t'aider sur ce sujet.
Mais il y a d'autre membres qui seront plus apte à te répondre.
Patiente un peu.
A+

jjsteing · Answer

bonsoir,

Ben j'ai beau regarder tes formule.. j vois pas comment optimiser.. t'as une formule différente pour chaque ligne, donc pas d optimisation possible..

Mais pour la question de lenteur.. lermite222 a trouver ta solution ;)

sinon, y a pas mieux..

VBA optimisation formules qui prennent du tps

4 réponses

Discussions similaires