Results 1 to 9 of 9
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Double Quotes Broken (2000)

    Hi All,

    I seem to have broken my double quotes. When typing an IF stmt and including double quotes for any of the parts (condition, true, false) for a constant string, Excel is giving me an error msg (see attached). If I hit OK on the error msg, Excel highlights the part with the double quotes. If I change that to something w/o double quotes, Excel is happy, unless the next part of the IF also has double quotes.

    The format of the cell in the attached screen shot is general. This is in a brand new workbook but happens in existing workbooks as well.

    I also tried a simple formula like ="abc" or ="abc" & "def" and get the same error message.

    I've tried Jan Karel's problem solving steps and that hasn't helped. For example, there are no add-ins being loaded (not even the Analysis Toolpak) on this computer.

    This has happened recently but I can't think of anything that I would have been doing that would have caused this.

    Any ideas?

    TIA

    Fred

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Double Quotes Broken (2000)

    Fred,

    Does this persist if you quit Excel, shut down the computer and restart?

    Can you assign the formula in VBA, as follows:
    - Activate the Visual Basic Editor (Alt+F11)
    - Activate the Immediate window (Ctrl+G)
    - Type this instruction (note the use of double double quotes):
    <code>Range("B1").Formula = "=if(a1=4,""ok"",""not ok"")"</code>
    - Press Enter. Do you get an error message?

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Double Quotes Broken (2000)

    If you put a double quote in cell A1 and enter in B1 the formula:
    <pre>=code(A1)</pre>


    fo you get 34 or some other number?

    Steve

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Double Quotes Broken (2000)

    Hi Hans,

    I've restarted, shut down and turned on the PC to no avail. I've been doing this for the past few days and nothing has changed.

    As far as the VBA approach, that did work (see attached). I guess that's the good news??? <img src=/S/grin.gif border=0 alt=grin width=15 height=15> But I know you're not suggesting I use the VBA approach for inserting formulas needing double quotes. I await your next suggestion. Thanks.

    Fred

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Double Quotes Broken (2000)

    Hi Steve,

    good idea. I tried it and got 148, which is the value for either the opening or closing smart double quote, if I recall correctly. Looks like the latter when I zoom to 200% on it.

    So I checked Excel's AutoCorrect and there it was - a replacement of a dumb double quote with a smart closing double quote. I know I've never added that to Excel's AutoCorrect list because I'm not fussy about that kind of thing in Excel. I do use this in Word (and have had mixed results on the replacement, which has pretty much gone unsolved even after posting to the Word lounge). I know my wife would not have added this AutoCorrect item since she hardly uses Excel and would not have added an AutoCorrect item to it either.

    Is there any chance that playing around with this in Word would have affected Excel? In Excel, you only have an AutoCorrect list of items of replace x with y, of which the double quote was there until I just deleted it. In Word, you have the list also but you also have an AutoFormat As You Type set of checkboxes, where you can choose to replace dumb quotes by smart quotes (the one box applies to both single and double). I deleted the single quote replacement from the Excel AutoCorrect list and it was gone from the Word AutoCorrect list but the AutoFormat item was still checked. I went back to Excel and typed ="abc" and that worked; an IF function also worked.

    I also added back the double quote replacement in Word's AutoCorrect list and it was back in Excel's list (I believe they share one list). Now Excel stopped working again in this regard. So this is clearly the cause but the mystery of how this got there in the first place remains. I don't think I would have added this to Word's AutoCorrect list since it has the checkbox. I'll ask my wife when she's home but I don't think she would have done this in Word.

    Thanks.

    Fred

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Double Quotes Broken (2000)

    All Office applications use a common AutoCorrect list. Quotes should definitely NOT be in this list, as you have found out. If your wife added it in Word, it will affect Access, Excel and PowerPoint too. Otherwise, I have no idea how the quotes ended up in the AutoCorrect list - I have seen a lot of strange things in Office, but I have never had items added to the AutoCorrect list spontaneously.

    As you also found, you can set the smart quotes option in the AutoFormat As You Type tab in Word, this will not influence Excel.

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Double Quotes Broken (2000)

    Hans,

    I got your email response responding to my first response to you but it seems like it hasn't made it onto the web site yet. Since I'm online now, I decided to respond to your first response. Does all of this make sense?

    In any case, see my response to Steve. Problem fixed as described there.

    I'm not aware of any keyboard utility being installed on this PC. But you can consider the AUtoCorrect as a keyboard mapper.

    Thanks for the help.

    I hope you understood that my comment about not using VBA to insert double quotes was a joke.

    Fred

  8. #8
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Double Quotes Broken (2000)

    Hans,

    looks like the loop is closed. But I still don't see your second response to me on the web site (the one asking if I installed a keyboard utility) even tho I've received both of your emails and it looks like this one that I'm responding to was later than the "keyboard utility" email. I've also hit Refresh several times.

    Fred

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Double Quotes Broken (2000)

    Fred,

    I did post another reply, but by the time I submitted it, you had posted your reply to Steve, making mine irrelevant, so I deleted it immediately, and posted another reply.

    I'm glad this problem has been resolved, even if you don't know how the AutoCorrect entry came to be there.

Posting Permissions

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