Results 1 to 10 of 10
Thread: Formula (2003)

20041013, 16:54 #1
 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,A15A1,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",A15A1,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

20041013, 17:05 #2
 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

20041013, 18:29 #3
 Join Date
 Jan 2004
 Posts
 196
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Formula (2003)
Thanks. that works.
Jackal

20041018, 15:03 #4
 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"",A1A15,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 ",A1A5000,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

20041018, 15:47 #5
 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 18Oct04 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),A15000,5000),"")"John ... I float in liquid gardens
UTC 7ąDS

20041018, 16:27 #6
 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),A1A5000,A5000).
I left the 'A' off of the first formula at the end of the IF
drng.Formula = "=If(ISNUMBER(A27),A27A5026,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

20041018, 16:46 #7
 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),A27A5026,IF(ISBLANK(A27),"""",A5026))"
Steve

20041018, 16:51 #8
 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),A15000,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),A27A5026,A5026))"
</pre>
Legare Coleman

20041018, 17:03 #9
 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

20041018, 19:02 #10
 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