Results 1 to 10 of 10

Thread: Formula (2003)

  1. #1
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Formula (2003)

    When I type the following:
    .Formula = "=IF(CELL(TYPE,A1)=V,A15-A1,A15)"
    in Excel, I get #NAME?, this is b/c i need quotes around TYPE and V

    when I tried it this way:
    .Formula = "=IF(CELL("TYPE",A1)="V",A15-A1,A15)"
    in VB i get a compile error, b/c of the "TYPE"

    Can someone please tell me why???????? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    Thanks,
    Jackal

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Formula (2003)

    I think it's because you need two double quotes around TYPE and V

  3. #3
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Formula (2003)

    Thanks. that works.
    Jackal

  4. #4
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Formula (2003)

    Ok, I got the following to work just fine:

    drng.Formula = "=IF(CELL(""TYPE"",A1)=""V"",A1-A15,A15)"

    Now I am trying to add a second "IF" to this and having no luck

    drng.Formula = "=IF(CELL("TYPE",A1)="B",""",IF(CELL("TYPE",A1)="V ",A1-A5000,5000))"
    This is what i am needing. this is w/o the double quotes on the following:
    TYPE, B, ""(for blank), TYPE, V

    Is there another way to write double quotes, because this seems to be my problem?
    thanks,
    jackal

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Formula (2003)

    <P ID="edit" class=small>(Edited by JohnBF on 18-Oct-04 08:47. Fixed formula.)</P>Investigate using:

    =ISBLANK(cell) instead of CELL("TYPE",A1)="B"

    and

    =ISNUMBER(cell) instead of CELL("TYPE",A1)="V"

    Does this work for your task:

    drng.Formula = "=IF(ISBLANK(A1),IF(ISNUMBER(A1),A1-5000,5000),"")"
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Formula (2003)

    Thanks for the reply. I do like the ISNUMBER and ISBLANK

    the formula you gave me will not work, b/c if the cell A1 is blank, I need the active cell to be blank, else if (ISNUMBER(A1),A1-A5000,A5000).

    I left the 'A' off of the first formula at the end of the IF

    drng.Formula = "=If(ISNUMBER(A27),A27-A5026,IF(ISBLANK(A27),"",A5026))

    The "" is what is giving me problems. Is there a way to use ISBLANK to return an empty cell? Currently the active cell returns a '0' if the referenced cell is blank.
    thanks,
    jackal

  7. #7
    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: Formula (2003)

    You are trying to put quotes within quotes, try this:
    drng.Formula = "=If(ISNUMBER(A27),A27-A5026,IF(ISBLANK(A27),"""",A5026))"

    Steve

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula (2003)

    I think John's formula should have been:

    <pre>drng.Formula = "=IF(ISBLANK(A1),"""",IF(ISNUMBER(A1),A1-5000,5000))"
    </pre>


    and modifying that to do what it looks like you are trying to do in your latest formula it would be:

    <pre>drng.Formula = "=IF(ISBLANK(A27),"""",IF(ISNUMBER(A1),A27-A5026,A5026))"
    </pre>

    Legare Coleman

  9. #9
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Formula (2003)

    Thanks Steve & Lagre

    It is working to prefection.
    I thought I tried the quotes around the quotes before I ever posted and could not get it to work, oh well, now it does.


    thanks again
    jackal

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Formula (2003)

    Yep, thanks for picking up on that Legare. I got buried some corporate <img src=/w3timages/censored.gif alt=censored border=0>.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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