PDA

View Full Version : Seek help for value



apple
09-23-2007, 03:33 AM
Hi,

I would like to seek help for this condition as below:

Column D if got price value then column E will stated as Yes

If Column D dont have price value then column E will stated as NO

Please see my attachment

Can anyone help me? Thanks

qff
09-23-2007, 04:08 AM
Hi

enter the following formula in cell E2

=IF(D2=0,"No","Yes")

this will return "No" if the cell =0 or is blank.

regards
qff

apple
09-23-2007, 08:58 AM
Hi

I not understand the syntax. Can u show me the coding if else for the condition? Thanks

Bob Phillips
09-23-2007, 09:09 AM
range("E2").Value = iif(range("D2").Value=0,"No","Yes")

apple
09-23-2007, 09:15 AM
Hi,

Your example: range("E2").Value = iif(range("D2").Value=0,"No","Yes")


The above syntax, what it mean Value=0,"No","Yes"?

Am i right if i write like below? Do i still need add anything?

Sheet1.Range("E2") = if(range("D2").Value =0,"No","Yes")

Norie
09-23-2007, 09:38 AM
apple

Just type what qff posted into the cell E2 and copy down, it's just a simple formula.

apple
09-23-2007, 09:43 AM
Hi Norie,

I have error with the syntax.

Norie
09-23-2007, 09:50 AM
And that error is?

Perhaps it's a international setting issue?

What do you use for seperators? Commas (,) or semicolons ; ?

apple
09-23-2007, 09:55 AM
Hi,

I only write as similar below syntax.

Sheet1.Range("E2") = if(range("D2").Value =0,"No","Yes")

The error exist because i think havent complete yet the syntax. Can you guide me to write the condition.

Bob Phillips
09-23-2007, 09:55 AM
Hi,

Your example: range("E2").Value = iif(range("D2").Value=0,"No","Yes")


The above syntax, what it mean Value=0,"No","Yes"?

Am i right if i write like below? Do i still need add anything?

Sheet1.Range("E2") = if(range("D2").Value =0,"No","Yes")

Read my previous post.

apple
09-23-2007, 09:57 AM
Hi Xld,

Your post only one sentence syntax. Is it already completed post?

Norie
09-23-2007, 09:58 AM
apple

Where are you putting that?

It's not a formula, it's VBA code - is that actually what you are looking for?

If it is code you need to use the double i - it's an immediate if function.

apple
09-23-2007, 10:12 AM
HI Norie,

My syntax is as below. Please correct me if got error

For i = 2 To 65536
If Sheet1.Range("E2" & i) = "" Then Exit For
Sheet1.Range("E2" & i) = if(range("D2").Value =0,"No","Yes")
Next

Norie
09-23-2007, 10:14 AM
If it is code you need to use the double i - it's an immediate if function.

Bob Phillips
09-23-2007, 10:18 AM
Hi Xld,

Your post only one sentence syntax. Is it already completed post?

Yes

rory
09-23-2007, 10:19 AM
For i = 2 To 65536
If Sheet1.Range("E" & i) = "" Then Exit For
Sheet1.Range("E" & i) = iif(range("D2").Value =0,"No","Yes")
Next

note that this code will just stop if any of the cells in E are already blank

Bob Phillips
09-23-2007, 10:29 AM
That should be



For i = 2 To 65536
If Sheet1.Range("E" & i) = "" Then Exit For
Sheet1.Range("E" & i) = iif(range("D" & i).Value = 0, "No", "Yes")
Next

rory
09-23-2007, 10:32 AM
Yep, good catch.

apple
09-23-2007, 10:46 AM
Hi,

I still not i understand how can i set if my column D have value price and column E set as Yes. If my column D no price and my column E set as No.
I still cant see the below coding about my request.

For i = 2 To 65536
If Sheet1.Range("E" & i) = "" Then Exit For
Sheet1.Range("E" & i) = iif(range("D" & i).Value = 0, "No", "Yes")
Next

Can anybody explain. I am new to VBA

rory
09-23-2007, 11:07 AM
This bit:
iif(range("D" & i).Value = 0, "No", "Yes")
returns "No" if the cell in column D is zero, "Yes" if it is not zero.
This bit:
Sheet1.Range("E" & i) =
assigns that returned value to the cell in E. Is that what you want? Did you try it?

apple
09-26-2007, 11:44 AM
Hello,

Yes, this is what i want. Thank you for your help and guidance. I already done.

unmarkedhelicopter
10-01-2007, 05:20 AM
What is an "iif" function ? Both Bob and Rory seem to use it but I don't have that. Is it an "if" function that has been "appleised" e.g. iPod, iBook, iPhone ? :devil2:

rory
10-01-2007, 05:28 AM
You mean it's not in your VB Help?

unmarkedhelicopter
10-01-2007, 05:35 AM
It just says "Please rephrase your question"
I assume it means "go and buy 'iHelp' " :devil2:

rory
10-01-2007, 05:40 AM
From (my) VB Help:

IIf Function
Returns one of two parts, depending on the evaluation of an expression (http://javascript<b></b>:hhobj_4.Click()).
Syntax
IIf(expr, truepart, falsepart)
The IIf function syntax has these named arguments (http://javascript<b></b>:hhobj_5.Click()):


Part

Description


expr
Required. Expression you want to evaluate.
truepart
Required. Value or expression returned if expr is True.
falsepart
Required. Value or expression returned if expr is False.

Remarks
IIf always evaluates both truepart and falsepart, even though it returns only one of them. Because of this, you should watch for undesirable side effects. For example, if evaluating falsepart results in a division by zero error, an error occurs even if expr is True.

rory
10-01-2007, 05:42 AM
It's the Immediate If function; generally I prefer an If...Then...Else construct, unless I'm using it in an Access query.