Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Colorado, USA
    Posts
    144
    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. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,947
    Thanks
    0
    Thanked 204 Times in 185 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]

  3. #3
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Colorado, USA
    Posts
    144
    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

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,947
    Thanks
    0
    Thanked 204 Times in 185 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]

  5. #5
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Colorado, USA
    Posts
    144
    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
  •