R1C1 formulla error end of statement VBA

[Jenya]

Hi,
I have the below formula which works perfect in Excel but when I paste in the VBA it gives and error:Compile error Expected: end of statement and it highlights FRANCE. I have tried to put underscore in front of France but it gives error again => Invalid character. I can't put the formula in one line only so I have to break in two separate lines. Also this is just one part of a bigger macro. Thank you very much in advance!!!

ActiveCell.FormulaR1C1 = "=IF(LEFT(R[-1]C[1],6)=""AT_COM"",""AUSTRIA"",IF(LEFT(R[-1]C[1],6)=""BE_COM"",""BELUX"",IF(LEFT(R[-1]C[1],6)=""CP_COM"",""CZECH"",IF(LEFT(R[-1]C[1],6)=""CZ_COM"",""CZECH"",IF(LEFT(R[-1]C[1],6)=""DK_COM"",""DENMARK"",IF(LEFT(R[-1]C[1],6)=""FI_COM"",""FINLAND"",IF(LEFT(R[-1]C[1],6)=""FR_COM""," & _
""FRANCE"",IF(LEFT(R[-1]C[1],6)=""DE_COM"",""GERMANY"",IF(LEFT(R[-1]C[1],6)=""GR_COM"",""GREECE"",IF(LEFT(R[-1]C[1],6)=""IL_COM"",""ISRAEL"",IF(LEFT(R[-1]C[1],6)=""IT_COM"",""ITALY"",IF(LEFT(R[-1]C[1],6)=""ME_COM"",""MIDDLE EAST"",IF(LEFT(R[-1]C[1],6)=""NL_COM"",""NETHERLANDS"",IF(LEFT(R[-1]C[1],6)=""NO_COM"",""NORWAY"",IF(LEFT(R[-1]C[1],6)=""PL_COM"",""POLAND"",IF(LEFT(R[-1]C[1],6)=""PT_COM"",""PORTUGAL"",IF(LEFT(R[-1]C[1],6)=""RU_RUSSIA"",""RUSSIA"",IF(LEFT(R[-1]C[1],6)=""RU_ENT"",""RUSSIA"",IF(LEFT(R[-1]C[1],6)=""SEE_CO"",""SEE"",IF(LEFT(R[-1]C[1],6)=""ES_COM"",""SPAIN"",IF(LEFT(R[-1]C[1],6)=""SA_COM"",""SOUTH AFRICA"",IF(LEFT(R[-1]C[1],6)=""SE_COM"",""SWEDEN"",IF(LEFT(R[-1]C[1],6)=""CH_COM"",""SWITZERLAND"",IF(LEFT(R[-1]C[1],6)=""TR_COM"",""TURKEY"",IF(LEFT(R[-1]C[1],6)=""UK_COM"",""UK"",IF(LEFT(R[-1]C[1],6)=""UK_ENT"",""UK PS"",""UNKNOWN""))))))))))))))))))))))))))"


End Sub

 

[AlphaFrog]

You should have three quotes in front of FRANCE

"""FRANCE""

 

[Jenya]

Oh God Bless You!! It worked!!! Is this a rule? When you split a formula you have to start the new line with quotes and put &_ at the end of the 1st line?

 

[AlphaFrog]

You're welcome.

Yes. That's how you split a string and the formula is a string.

 

[Jenya]

Thanks a lot for your help!

I have sorted this but I want to copy the formula down now. I have added this line but it gives run time error 1004..

ActiveCell.Offset(1, -1).autofill Destination:=ActiveCell.Range("A1 & lrow"), Type:=xlFillDefault

Also this formula is dynamic and it could be entered in a different column (A,B,C etc). Does it make any difference when you set the destination? When you put A1 does it mean that the formula has to be copied in column A only?

I have declared my lrow as below
Dim lrow As Long
lrow = Cells(Rows.Count, 1).End(xlUp).Row

 

[AlphaFrog]

[color=darkblue]With[/color] ActiveCell.Offset(1, -1)
    .AutoFill Destination:=.Resize(lrow - .Row + 1, 1), Type:=xlFillDefault
[color=darkblue]End[/color] [color=darkblue]With[/color]