Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Mar 2005
    Location
    Kent, England
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Can anyone help .... please?

    I have been handed over a very nasty piece of Excel development that requires "tweaking" and have come across an issue with a string. The result required for the cell is "=IF(ISERROR(123456), "[]", 123456)" which if I manually input within the application works as expected. programmatically it throws an error as the square brackets are not appearing within speech marks. The line of code reads:

    strAfter = "=if(iserror(" & strAfter & "), " & "[]" & ", " & strAfter & ")"
    celCell.Formula = strAfter

    The string appears as "=IF(ISERROR(123456), [], 123456)"

    I have tried a number of ways to ensure the square brackets sit within speech marks but everything I try seems to default to removing the speech marks.

    Has anyone come across this before?

    Many thanks
    Jack

  2. #2
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    Try using two " characters wherever you want one to appear in the actual string
    strAfter = "=if(iserror(" & strAfter & "), " & """[]""" & ", " & strAfter & ")"

  3. #3
    2 Star Lounger
    Join Date
    Mar 2005
    Location
    Kent, England
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You star Stuart. I had tried double quotes but no joy, never thought of triple quotes.

    Thanks so much
    Jack

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could simplify it slightly to

    strAfter = "=if(iserror(" & strAfter & "),""[]""," & strAfter & ")"

Posting Permissions

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