Strange constant evaluation

[luggw1]

I have the following constant declaration in my 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

[macropod]

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:

Private Const strREMAINING_HOURS As String = _
    "=IF(COUNTIF(F#:CY#,<>""&"")=0,C#,LOOKUP(2,1/(F#:CY#<>""""),F#:CY#))"

[luggw1]

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:

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:

        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

[macropod]

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:

Private Const strREMAINING_HOURS As String = _
    "=IF(COUNTIF(F#:CY#,"" <> ""&"""")=0,C#,LOOKUP(2,1/(F#:CY#<>""""),F#:CY#))"

[luggw1]

Hi Bill,



You could use:

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