Break line of code into multiple lines

[coop123]

Hi

I am trying to split a long line of code into multiple line, I have entered space then under score and hit carrage return but keep getting error message compile error: expected end of statement. could someone explain what I am doing wrong.

ActiveCell.FormulaR1C1 = _
"=IF(RC[-3]=1201,""MWO GEN"",IF(RC[-3]=1202,""PROD GEN"",IF(RC[-3]=1203,""INC INSP"",IF(RC[-3]=1221,""MWO PROD"",IF(RC[-3]=3011,""PURCH"",IF(RC[-3]=3041,""MAINT"",IF(RC[-3]=3201,""ENG"",IF(RC[-3]=5000,""MD OFFICE"",IF(RC[-3]=5011,""MD OFFICE"",IF(RC[-3]=5042,""QC"",IF(RC[-3]=5051,""FINANCE"",IF(RC[-3]=5052,""HR"",IF(RC[-3]=5053,""EDP"",IF(RC[-3]=5054,""EDP""))))))))))))))"

See code with split entered giving error message

ActiveCell.FormulaR1C1 = _
"=IF(RC[-3]=1201,""MWO GEN"",IF(RC[-3]=1202,""PROD GEN"", _
IF(RC[-3]=1203,""INC INSP"",IF(RC[-3]=1221,""MWO PROD"",IF(RC[-3]=3011,""PURCH"",IF(RC[-3]=3041,""MAINT"",IF(RC[-3]=3201,""ENG"",IF(RC[-3]=5000,""MD OFFICE"",IF(RC[-3]=5011,""MD OFFICE"",IF(RC[-3]=5042,""QC"",IF(RC[-3]=5051,""FINANCE"",IF(RC[-3]=5052,""HR"",IF(RC[-3]=5053,""EDP"",IF(RC[-3]=5054,""EDP""))))))))))))))"

Thanks for any assistance

coop123

 

[RoryA]

You can't split within the formula string as the underscore will become part of the formula. You need to close the string and then concatenate with the next line - eg:

ActiveCell.FormulaR1C1 = _
"=IF(RC[-3]=1201,""MWO GEN"",IF(RC[-3]=1202,""PROD GEN""," & _
"IF(RC[-3]=1203,""INC INSP"",IF(RC[-3]=1221,""MWO PROD"",IF(RC[-3]=3011,""PURCH"",IF(RC[-3]=3041,""MAINT"",IF(RC[-3]=3201,""ENG"",IF(RC[-3]=5000,""MD OFFICE"",IF(RC[-3]=5011,""MD OFFICE"",IF(RC[-3]=5042,""QC"",IF(RC[-3]=5051,""FINANCE"",IF(RC[-3]=5052,""HR"",IF(RC[-3]=5053,""EDP"",IF(RC[-3]=5054,""EDP""))))))))))))))"

 

[Alex Blakenburg]

This is not answering your question but I think you would be better served by having a lookup table which is much easier to maintain than a very messy nested if statement.
Sample below:

20221222 VBA Vlookup instead of nested IF coop123.xlsm
	A	B	C	D	E	F
1
2
3	Cost Centre	Description		Cost Centre	3011
4	1201	MWO GEN		Vlookup	PURCH	=VLOOKUP(E3,$A$4:$B$17,2,FALSE)
5	1202	PROD GEN
6	1203	INC INSP
7	1221	MWO PROD
8	3011	PURCH
9	3041	MAINT
10	3201	ENG
11	5000	MD OFFICE
12	5011	MD OFFICE
13	5042	QC
14	5051	FINANCE
15	5052	HR
16	5053	EDP
17	5054	EDP
VLookup

Cell Formulas
Range	Formula
E4	E4	=VLOOKUP(E3,$A$4:$B$17,2,FALSE)
F4	F4	=FORMULATEXT(E4)

 

[coop123]

Hi Rory

That solved my problem. It makes sence now you have explained.

Thanks again

coop123

 

[coop123]

Hi Alex

Thanks for your reply. I'll give it a try.

Thanks

coop123