#1
  1. 63 dorinte
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2005
    Location
    Alleghany Highlands
    Posts
    130
    Rep Power
    230

    Escape Double Quotes in VBA for Excel?


    I'm having trouble with an Excel cell formula that is generated in VBA. The formula needs to contain an empty double quote (i.e. ""). If I use a double-double quote (i.e. """") to escape the double quotes, I get a Runtime Error 1004.

    This line, inside a For .. Next loop, correctly copies to the spreadsheet cell:
    Code:
    Worksheets("QuoteForm").Range("F" & CurrentRow).Formula = "=IF(OR(ISBLANK(D" & CurrentRow & "),ISBLANK(J" & CurrentRow & "))," & "" & ",(D" & CurrentRow & ")*(J" & CurrentRow & "))"[
    but without the needed double quotes:
    Code:
    =IF(OR(ISBLANK(D17),ISBLANK(J17)),,(D17)*(J17))
    when I really needed:
    Code:
    =IF(OR(ISBLANK(D17),ISBLANK(J17)),"",(D17)*(J17))
    When I double the double quotes:
    Code:
    Worksheets("QuoteForm").Range("F" & CurrentRow).Formula = "=IF(OR(ISBLANK(D" & CurrentRow & "),ISBLANK(J" & CurrentRow & "))," & """" & ",(D" & CurrentRow & ")*(J" & CurrentRow & "))"
    it gives the Runtime Error 1004.

    Am I missing something simple, or is there another way to escape the double quotes?

    Thanks,

    Ben N1NP
  2. #2
  3. 63 dorinte
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2005
    Location
    Alleghany Highlands
    Posts
    130
    Rep Power
    230
    I got it . To escape a single double quote (") it takes two: """. Then a double-double quote ("") takes twice that: """""".

    Thanks anyway (all it took was posting for the answer to magically appear ).

    Ben N1NP
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    2
    Rep Power
    0

    get data in same format


    I want to put this data in a string using quotes and to get the same data in same fomat after executing in VBA.Please help me. replies will be appriciated.thnks in advance.

    {
    "Phone": "4029303500",
    "ResourceType": "Enhanced"
    }
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    289
    Rep Power
    45
    Originally Posted by n1np
    I got it . To escape a single double quote (") it takes two: """. Then a double-double quote ("") takes twice that: """""".

    Thanks anyway (all it took was posting for the answer to magically appear ).

    Ben N1NP
    In VB6, 4 upper case quotes """" produces a single quote ", so you would need 2. In SQL statements, a lower case quote works as well as upper case, so a simpler solution would be "''" which produces '' (2 lower case quotes).

    J.A. Coutts
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    20
    Rep Power
    0
    To insert either a single or double quote into any string for any purpose.....

    Declare a character string and initialize it to the ascii of the single or double quote

    Then merely concatenate that character into your desired string

    Example

    dq = chr(34) 'double quote
    sq = chr(39) 'single quote

    mystring = "abcdefg" & dq & "xyz"

    I've been doing this for years.... makes life really easy, rather than trying to remember the 4 quotes, etc.

IMN logo majestic logo threadwatch logo seochat tools logo