Results 1 to 7 of 7

Thread: & sign (03)

  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    & sign (03)

    How does one programatically include an "&" within this line of code?

    Range("A1").formula = "=CountA(A2:A1000) & " Total"

    Thanks,
    John

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

    Re: & sign (03)

    I think your question is actually: how does one programmatically include a quoted string in a formula. Answer: double the quotes.
    <code>
    Range("A1").Formula = "=CountA(A2:A1000)&"" Total"""</code>

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: & sign (03)

    Hans,

    Thanks. I was wondering how to state the question without confusing anyone.

    Regards,
    John

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: & sign (03)

    Hans,

    Let's take it one step further where there is a variable involved such as "oVar1"

    Based on your suggestion and oVar1 = "Total"

    Proposed code which does not work.
    Range("A1").Formula = "=CountA(A2:A1000)&"" & oVar1 & """

    John

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

    Re: & sign (03)

    Too few quotes...
    <code>
    Range("A1").Formula = "=CountA(A2:A1000)&""" & oVar1 & """"</code>

  6. #6
    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: & sign (03)

    Too avoid confusion I sometimes will create a variable to hold the dbl quote and use this for the "internal ones"

    sDQ=chr(34)
    oVar1="Total"
    Range("A1").Formula = "=CountA(A2:A1000) & " & sDQ & oVar1 & sDQ

    Steve

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

    Re: & sign (03)

    Or if you need it only a couple of times
    <code>
    Range("A1").Formula = "=CountA(A2:A1000)&" & Chr(34) & oVar1 & Chr(34)</code>

Posting Permissions

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