Excel formula doesn't work when recorded as macro - syntax error

[Welldonedav]

hi,

i have a formula that works in excel but when i come to record it as a macro it no longer works and si ignored but doesn't diurupt any other part of the macro. Recording below

Range("O3").Select
ActiveCell.FormulaR1C1 = _
"=IF(OR(RC[-9]=""TY"",RC[-9]=""1M"",RC[-9]=""2M"",RC[-9]=""3M"",RC[-9]=""5M""),CONCATENATE(TRIM(RC[-7])/10,TRIM(RC[-8])),CONCATENATE(TRIM(RC[-7]),TRIM(RC[-8])))"


When i finish recording it shows a syntax error but shows the correct result in the square on excel.

When the macro is run the result is always the IF false outcome even if the IF is TRUE.

I would greatly appreciate anyones help/advice on this as i'm pretty new to vba

thanks

 

[Andrew Poulsom]

Welcome to MrExcel.

There is no syntax error in that code (Excel 2010).

 

[njimack]

What do you have in H3, F3 and G3?

By the way, your formula could be condensed to:
=TRIM(H3)/IF(OR(F3={"TY","1M","3M","5M"}),10,1)&TRIM(G3)

 

[Welldonedav]

thanks v.much
ok thats good, any ideas why it might not be working then?

 

[Welldonedav]

in H3,F3 and G3 there are product codes and expiry details which I am trying to concatenate.

thanks for the condensed formula, where would I input that it to the code? it doesn't seem to work if I just substitute it for what i already have. apoloiges if these are v.basic questions

 

[njimack]

in H3,F3 and G3 there are product codes and expiry details which I am trying to concatenate.

thanks for the condensed formula, where would I input that it to the code? it doesn't seem to work if I just substitute it for what i already have. apoloiges if these are v.basic questions

What values are in the 3 cells the formula refers to?
The formula I posted was the Excel version of your VBA formula. The VBA version would be (note that you don't need to select a cell to write a formula to it):

Range("O3").FormulaR1C1 = "=TRIM(RC[-7])/IF(OR(RC[-9]={""TY"",""1M"",""3M"",""5M""}),10,1)&TRIM(RC[-8])"

 

[Welldonedav]

[TABLE="width: 640"]
<TBODY>[TR]
[TD][/TD]
[TD]Market</SPAN>[/TD]
[TD]B/S</SPAN>[/TD]
[TD]Volume</SPAN>[/TD]
[TD] Expiry</SPAN>[/TD]
[TD]Contract</SPAN>[/TD]
[TD]C/P</SPAN>[/TD]
[TD]Strike</SPAN>[/TD]
[TD]Price</SPAN>[/TD]
[TD]AVG Price</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Trade Details</SPAN>[/TD]
[TD]CME</SPAN>[/TD]
[TD]S</SPAN>[/TD]
[TD="align: right"]100</SPAN>[/TD]
[TD] DEC3</SPAN>[/TD]
[TD] ES</SPAN>[/TD]
[TD] P</SPAN>[/TD]
[TD="align: right"]1700</SPAN>[/TD]
[TD="align: right"]0.55</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]CBOT</SPAN>[/TD]
[TD]S</SPAN>[/TD]
[TD="align: right"]100</SPAN>[/TD]
[TD] DEC3</SPAN>[/TD]
[TD] TY3</SPAN>[/TD]
[TD] P</SPAN>[/TD]
[TD="align: right"]1235</SPAN>[/TD]
[TD="align: right"]0.07</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]CBOT</SPAN>[/TD]
[TD]S</SPAN>[/TD]
[TD="align: right"]100</SPAN>[/TD]
[TD] DEC3</SPAN>[/TD]
[TD] TY3</SPAN>[/TD]
[TD] P</SPAN>[/TD]
[TD="align: right"]1220</SPAN>[/TD]
[TD="align: right"]0.01</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]CBOT</SPAN>[/TD]
[TD]B</SPAN>[/TD]
[TD="align: right"]200</SPAN>[/TD]
[TD] DEC3</SPAN>[/TD]
[TD] TY3</SPAN>[/TD]
[TD] P</SPAN>[/TD]
[TD="align: right"]1225</SPAN>[/TD]
[TD="align: right"]0.01</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL span=10></COLGROUP>[/TABLE]

 

[Welldonedav]

i adjust the expiry and and product codes earlier in the macro so F3 which is Contract changes, TY3 becomes 3M which should trigger a change in the way G3 and H3 then contatenate as the strike price should become 123.5, 122, and 122.5 respectively....