Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Colorado, USA
    Posts
    142
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have the following constant declaration in my code:

    Code:
    Private Const strREMAINING_HOURS As String = _
        "=IF(COUNTIF(F#:CY#," <> "&"")=0,C#,LOOKUP(2,1/(F#:CY#<>""),F#:CY#))"
    When I run it, strREMAINING_HOURS evaluates to TRUE insteado fo the string I expected to find there. Any ideas as to why this is happening and how to prevent it?

    Thanks for the help.
    Bill Lugg

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,781
    Thanks
    0
    Thanked 162 Times in 150 Posts
    Hi Bill,

    The problem is in the way you've used unequal pairs of double quotes. I'm not sure how the final output is supposed to appear, but you might try:
    Code:
    Private Const strREMAINING_HOURS As String = _
        "=IF(COUNTIF(F#:CY#,<>""&"")=0,C#,LOOKUP(2,1/(F#:CY#<>""""),F#:CY#))"
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  4. #3
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Colorado, USA
    Posts
    142
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by macropod View Post
    Hi Bill,

    The problem is in the way you've used unequal pairs of double quotes. I'm not sure how the final output is supposed to appear, but you might try:
    Code:
    Private Const strREMAINING_HOURS As String = _
        "=IF(COUNTIF(F#:CY#,<>""&"")=0,C#,LOOKUP(2,1/(F#:CY#<>""""),F#:CY#))"
    Yep, should have been as plain as the nose on my face. I needed the quotes where they are in the original string for the formulat to work when I insert it into a cell, so I had to replace them with "Chr$(34)" calls and put it in a variable instead of a const (I don't know of a way of escaping the quotes VB in the string like you can in C/C++).

    This is how it turned out:
    Code:
            strRemainingHours = _
            "=IF(COUNTIF(F#:CY#," & Chr$(34) & " <> " & Chr$(34) & "&" & _
                Chr$(34) & Chr$(34) & ")=0,C#,LOOKUP(2,1/(F#:CY#<>" & Chr$(34) & _
                Chr$(34) & "),F#:CY#))"
    Sometimes you get so close to something you can't see the obvious problems.

    Again, thanks for the help.
    Bill Lugg

  5. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,781
    Thanks
    0
    Thanked 162 Times in 150 Posts
    Hi Bill,

    I had to replace them with "Chr$(34)" calls and put it in a variable instead of a const (I don't know of a way of escaping the quotes VB in the string...).
    You could use:
    Code:
    Private Const strREMAINING_HOURS As String = _
        "=IF(COUNTIF(F#:CY#,"" <> ""&"""")=0,C#,LOOKUP(2,1/(F#:CY#<>""""),F#:CY#))"
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  6. #5
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Colorado, USA
    Posts
    142
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by macropod View Post
    Hi Bill,



    You could use:
    Code:
    Private Const strREMAINING_HOURS As String = _
        "=IF(COUNTIF(F#:CY#,"" <> ""&"""")=0,C#,LOOKUP(2,1/(F#:CY#<>""""),F#:CY#))"
    That was the ticket. Thanks!

    I appreciate the help.
    Bill Lugg

Posting Permissions

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