Continue line within formula

gyme623

New Member
Joined
Jun 6, 2008
Messages
14
Hello,

I am using a macro to enter a long if statement formula, and I just can't seem to make the "&_" work to continue to the next line within the formula.

My code is:

ActiveCell.FormulaR1C1 = "=IF(AND(RC[-2]>=0,RC[-2]<100),""GOVT"", IF(AND(RC[-2]>100,RC[-2]<250),""MTGE"", IF(AND(RC[-2]>250,RC[-2]<400),""MUNI"", IF(RC[-2]>400,""CORP""))))"


Any suggestions?

Thanks
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,022
just break it into sub-strings enclosing each line's string in quotes:
Code:
ActiveCell.FormulaR1C1 = "=IF(AND(RC[-2]>=0,RC[-2]<100),""GOVT"", " & _
                         "IF(AND(RC[-2]>100,RC[-2]<250),""MTGE"", " & _
                         "IF(AND(RC[-2]>250,RC[-2]<400),""MUNI"", " & _
                         "IF(RC[-2]>400,""CORP""))))"
In good conscience, I should probably mention that you probably deserve to be chastised for something if you are using activecell. Chances are real good you're doing a SELECT when you needn't. But I cannot convict you based on the evidence presented. :wink:
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,268
Messages
5,836,301
Members
430,419
Latest member
silvaleroy

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