0

I am needing to insert a formula in using vba coding.

at the moment the line of code is

=IF(
    AND(
        Compliance="No",
        OR(
            Delivery <> "",
            C31 <> ""
        )
    ),
    "Please Delete Compliance Data",
    IF(
        AND(
            E11="",
            E13="",
            E23="",
            E25="",
            E26="",
            E28="",
            E30="",
            E31=""),
        VLOOKUP(C15,'Extrapolated RV Calculator'!B:S,18,0),
        "Please complete form / SC request"
    )
)"

It seems to error out on the text portions inside the code.

Any help would be appreciated.

Improve this question
8
  • 1
    If you have quotes inside a string then you need to escape them by doubling them up s = "a string with ""quotes"" in it" Commented Mar 20, 2013 at 1:18
  • Thanks, I changed it to Range("RV").Formula = "=IF(AND(Compliance=""No"",OR(Delivery<>"""",C31<>"""")),""Please Delete Compliance Data"",IF(AND(E11="""",E13="""",E23="""",E25="""",E26="",E28="""",E30="""",E31=""""),VLOOKUP(C15,'Extrapolated RV Calculator'!B:S,18,0),""Please complete form / SC request""))" But it comes up with 400 in a VBA error box Commented Mar 20, 2013 at 1:41
  • 2
    If you assign the formula to a variable and Debug.print it, can you copy the output from the Immediate window and paste it into the cell without any error? If it gives you an error, what is it? Commented Mar 20, 2013 at 2:13
  • Thanks for all your help Tim, the error was on E13, where I stil only had 1 set of brackets. Commented Mar 20, 2013 at 2:59
  • my experience with inserting formulas is to break them into very simple pieces (across several cells) at least to start with, and get the pieces working independently. Tim's suggestion is also a best practice IMO Commented Mar 20, 2013 at 3:01

2 Answers 2

Reset to default
2

Here are four ways to use quotation marks inside strings in VBA:

  • Replace "" with Chr$(34). 34 is the ASCII code for quotation marks
  • Use String(2,Chr$(34)) to make double quotes
  • Use two double quotation marks for every one (per Tim's comment)
  • Replace two double quotation marks with a different method, like ISBLANK for worksheet functions.

None of these is better. I usually use two double quotation marks to escape them. But sometimes I get so many quotation marks together that it's hard to read and maintain, so I'll use a different method. Here's an example:

Dim aFormula(1 To 5) As String

aFormula(1) = "=IF(AND(Compliance=""No"",OR(Delivery<>" & String(2, Chr$(34))
aFormula(2) = ",C31<>" & String(2, Chr$(34)) & ") ),"
aFormula(3) = Chr$(34) & "Please Delete Compliance Data" & Chr$(34)
aFormula(4) = ",IF(AND(ISBLANK(E11),ISBLANK(E13),ISBLANK(E23),ISBLANK(E25),ISBLANK(E26),ISBLANK(E28),ISBLANK(E30),ISBLANK(E31)),"
aFormula(5) = "VLOOKUP(C15,'Extrapolated RV Calculator'!B:S,18,0),""Please complete form / SC request""))"

Sheet1.Range("R13").Formula = Join(aFormula, vbNullString)
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

To empower you for the future I strongly recommend you do the following when you want to use functionality in Excel in your VBA code that you are not acquainted with yet.

Go to the developer menu and press the record macro button.

Now fill in a formula in a cell, Clear a cells content by pressing delete and clearing a cells content by going into the cell and deleting all the characters and press enter.

Now stop the macro. And go to the VBA window. See how the code is structured to accomplish these things.

Apply this also whenever you want to do other things to get a good idea of what you can use to get the results you want without having to browse around on the internet for ages!

Also watch the local variables in the locals window or Debug.Print the string you try to build to see hwat it looks so far

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.