+ Reply to Thread
Results 1 to 5 of 5

VBA- Compile Error, Expected: line number or label or statement or end of statement

  1. #1
    Registered User
    Join Date
    05-16-2012
    Location
    New York, New York
    MS-Off Ver
    Excel 2010
    Posts
    2

    VBA- Compile Error, Expected: line number or label or statement or end of statement

    Hi- this is my first time posting in the forum as I am a newer VBA user. I hope you can help.

    ActiveCell.FormulaR1C1 = _
    ""
    I am trying to paste the formulas below into the "" above and am getting an error. It says Compile Error, Expected: line number or label or statement or end of statement. I suspect that this error might be coming up because I have too many functions in one cell. The formula below works in excel do you know why it doesn't work in VBA? Is there a better way to do this? I think this might be a beginner error.

    =IF(OR(RC[-5]="Default to MIP pricing",RC[-5]="Not Traded"),"Not Traded",IF(RC[2]="IG",IF(AND(OR(RC[-5]="Priced to Maturity",RC[-5]="Priced to Call"),RC[-21]<>R1C3),"TRACE >$1mm traded on "&TEXT(RC[-21],"mm/dd/yyyy")&" at "&MROUND(RC[-20],0.01)&" / "&MROUND(RC[-15],1)&"bps adjusted to "&MROUND(RC[-9],1)&"bps based on "&IF(AND(RC[-12]<>"",ABS(RC[-12])<0.1),"CDS change of "&MROUND(ABS(RC[-12]*100),0.01)*SIGN(RC[-12])&"%","LUCI change of "&MROUND(ABS(RC[-11]*100),0.01)*SIGN(RC[-11])&"%"),IF(AND(OR(RC[-5]="Using Trade PX",RC[-21]=R1C3),ISERROR("TRACE >$1mm traded on "&TEXT(RC[-21],"mm/dd/yyyy")&" at "&MROUND(RC[-20],0.01)&" / "&MROUND(RC[-15],1)&"bps")),"TRACE >$1mm traded on "&TEXT(RC[-21],"mm/dd/yyyy")&" at "&MROUND(RC[-20],0.01),"TRACE >$1mm traded on "&TEXT(RC[-21],"mm/dd/yyyy")&" at "&MROUND(RC[-20],0.01)&" / "&MROUND(RC[-15],1)&"bps")),IF(RC[-21]>R5C3,"TRACE >$1mm traded on "&TEXT(RC[-21],"mm/dd/yyyy")&" at "&MROUND(RC[-20],0.01),"TRACE >$1mm traded on "&TEXT(RC[-21],"mm/dd/yyyy")&" at "&MROUND(RC[-20],0.01)&" adjusted by HY index change of "&MROUND(ABS(RC[-11]*100),0.01)*SIGN(RC[-11])&"%")))

    Hopefully you can help!

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,129

    Re: VBA- Compile Error, Expected: line number or label or statement or end of statement

    The formula you are putting in the active cell is a text string. A text string in VBA starts and ends with "double quotes". If you want to have literal double quotes within your text string, you represent each literal double quote with two double quotes e.g.

    Please Login or Register  to view this content.
    I didn't consider the efficacy of your actual formula.
    Last edited by AlphaFrog; 05-16-2012 at 04:26 PM.

  3. #3
    Registered User
    Join Date
    05-16-2012
    Location
    New York, New York
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: VBA- Compile Error, Expected: line number or label or statement or end of statement

    Hi Alpha Frog,
    I would have done that but it won't let me enter that into VBA. I always input the double quotes when pasting it into VBA. It looks like it is too long. Any other suggestions?

  4. #4
    Valued Forum Contributor StevenM's Avatar
    Join Date
    03-23-2008
    Location
    New Lenox, IL USA
    MS-Off Ver
    2007
    Posts
    910

    Re: VBA- Compile Error, Expected: line number or label or statement or end of statement

    Perhaps there is a better way, but if you add & DQ & between your double quotes, it will work.

    Please Login or Register  to view this content.

  5. #5
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,129

    Re: VBA- Compile Error, Expected: line number or label or statement or end of statement

    You could breakout the bits that are repetitive in the formula and calculate them in separate cells e.g. calculate in another cell
    ="TRACE >$1mm traded on "&TEXT(RC[-21],"mm/dd/yyyy")&" at "&MROUND(RC[-20],0.01)
    Then reference that cell within the long formula.

    The ISERROR function...
    ISERROR("TRACE >$1mm traded on "&TEXT(RC[-21],"mm/dd/yyyy")&" at "&MROUND(RC[-20],0.01)&" / "&MROUND(RC[-15],1)&"bps")
    ...doesn't need to include all the text. It only needs to test the calculation part. The concatenated text won't cause the error.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1