RE: Formula using range name in VBA code…. HELP - SOS

[Naceur]

Hi every one,







I SENT THIS FEW DAYS AGO BUT I STILL DIDN'T RECEIVE ANY ANSWER. PLEASE CAN SOME ONE HELP ME?







Could some one help me to translate this formula to VBA code?



My objective is to calculate the value inside the macro and putting the results directly in the right columns.



Let’s give an example:





In the columns “H” I have the following formula:



H11 = (INDEX(CUSTOCAD,,MATCH((IF(ISERROR(FIND(".",$E11)),$E11,LEFT($E11,LEN($E11)-2))),CAPDESPE,0))) : Formula 1





In the columns “I” I have the following formula:



I11 = IF($F11="USD",$G11/TXCAMBIO, $G11) : Formula 2





In the columns “J” I have the following formula:



J11 = ($D11)*(100+$H11+$I11)/100 : Formula 3



CUSTOCAD, CAPDESPE and TXCAMBIO are range name.





I would like that the VBA code make the following:



Value1 = Formula 1

Cells(H11) = value1



Value2 = Formula 2

Cells(I11) = value2



Value3 = Formula 3

Cells(J11) = value3



How can I convert the Formula1, Formula2 and Formula3 in VBA code?





Thanks

Naceur

[jindon]

Can't you just use like

Range("H11").Formula="= (INDEX(CUSTOCAD,,MATCH((IF(ISERROR(FIND(""."",$E11)),$E11,LEFT($E11,LEN($E11)-2))),CAPDESPE,0)))"

or

Range("H11")=evaluate("(INDEX(CUSTOCAD,,MATCH((IF(ISERROR(FIND(""."",$E11)),$E11,LEFT($E11,LEN($E11)-2))),CAPDESPE,0)))"

if your formula is working on the sheet, it should work, but I wonder if your formula works.

(INDEX(CUSTOCAD,,MATCH... two comma?