R1C1 formulla error end of statement VBA

Jenya

New Member
Joined
Jul 2, 2012
Messages
24
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
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,377
You should have three quotes in front of FRANCE

"""FRANCE""
 
Last edited:

Jenya

New Member
Joined
Jul 2, 2012
Messages
24
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,377
You're welcome.

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

Jenya

New Member
Joined
Jul 2, 2012
Messages
24
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,377
Code:
[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]
 

Watch MrExcel Video

Forum statistics

Threads
1,122,421
Messages
5,596,033
Members
414,039
Latest member
southike

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top