Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    ' in Macro for Excel Formula (Excel 2002)

    Hi,
    I have the following code in a macro and it doesn't like the quotes I use for defining the cell as BLANK (as opposed to zero)... it works if I put a zero in it instead of the "". Of course the formula works in Excel, but VBA gets confused because of my quotes... is there another option for telling it if it is BLANK (I don't want it to be zero), then, etc... I'm needing the actual formula in Excel, so this is why I have it coded the way I do. Any help is always appreciated!
    Thanks!!!
    Lana

    Range("K9") = "=VLOOKUP(J9,$AE$2:$AG$13,3,FALSE)"
    Range("L9") = "=G9*K9"
    Range("M9") = "=IF(H9="","",H9)"
    Range("N9") = "=IF(G9<>"",E9*(1+L9),E9+M9)"
    Range("O9") = "=N9/D9"

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

    Re: ' in Macro for Excel Formula (Excel 2002)

    If you want to use a quote <code>"</code> within a quoted string, you must double it: <code>""</code>. As a consequence, you must use <code>"""</code> for <code>""</code>. For example:
    <code>
    Range("M9") = "=IF(H9="""","""",H9)"
    </code>
    You can also test for a cell being really blank by using the ISBLANK function:
    <code>
    Range("M9") = "=IF(ISBLANK(H9),"""",H9)"
    </code>
    Note that if H9 contains a formula that returns <code>""</code>, ISBLANK(H9) will be FALSE. It will only be TRUE if H9 is really empty.

    Also note that the formula <code>=IF(H9="","",H9)</code> is equivalent to <code>=H9</code> so you actually don't need IF here.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: ' in Macro for Excel Formula (Excel 2002)

    This makes sense...thanks so much Hans! Note that my formula was supposed to have H9*K9, so that's why I had the IF, but good catch though, that would have been silly of me to have it that way, I can see why you mentioned it )
    Thanks again Hans!
    Lana

Posting Permissions

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