Newbie IF

J

Jeff

I know I'm new to VB but this seems pretty simple and I'm
stuck.
I hate to ask dumb questions here but I've been looking at
this for an hour! Maybe it's late in the evening...

If ("d237" = "d239") Then
Range("d241").Value = "1"
Else
Range("d241").Value = "2"
End If

I'm returning "2" but it shouldn't be. Currently d237 does
equal d239. The only thing I can think of that could be
causing a problem is d237 is a formula and d239 is a cell
reference to another sheet.
 
S

SmilingPolitely

This is because you are asking Excel to compare the two strings "d237"
and "d239", which of course are not equal so the code will return 2.

I am pretty sure that yuo want to be comparing the values of the cells:

If Range("d237").Value = Range("d239").Value Then
Range("d241").Value = "1"
Else
Range("d241").Value = "2"
End If



Hope this helps.... and don't work late!


Scott
 
J

JeffP

It seemed like a simple fix,but it still doesn't work.
BTW, a simple If Function does work but I'm trying to learn VB.

This function returns a "1"

=IF(D239=D237,"1","2")

Still wonderin'
jef
 
B

Bob Phillips

It's VBA Jeff

Range("D241") = IIf(Range("D237") = Range("D239"), "1", "2")

or

Range("D241") = (Range("D237") = Range("D239")) * 1 + 2

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
J

JeffP

Very nice and very clean. I never thought of IIf (why
would I?)and I don't really understand the second
statement. Still, I had to add .text property to the Range
property. .Value property returns false also.
Range("d242")= If(Range("d237").text = Range
("d239").text,"1","2")
Why, I don't know ... but I did some testing by entering a
dummy cell w/ a number and using it against first d237 and
then d239. The cell d237 is a formula (=D234-d235) and is
recognized as a number by .value or just plain Range.
However, the cell in D239, which is a reference to a cell
on another sheet has some kind of Text status, I guess.
The reference is ='Import Page'!C216 and this cell is a
formula (=c35-c45). Originally,c45 and c35 were imported
as text but I multiplied by 1 and used Paste Special
because of the problem w/ negative numbers being read as
text and therefore invisible to formulas.
Anyway......this is where I am and any education is
appreciated.
jeffP
 
B

Bob Phillips

Jeff,

The difference between Value and Text is that Text takes the format as well
as the value. I do not understand why you needed Text, my testing with a
formula in D239 made no difference. Oh well.

The second statement is basically testing D237 against D239, which will
return True or False. I multiply that by 1, to coerce to values -1 or 0. I
then add 2 to get 1 or 2. Not really recommended, but just to show the
technique.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
D

Dana DeLouis

is d237 is a formula

My guess is that it's a Rounding issue. Both numbers are displayed the same
thru formatting, but the underlying values are slightly different.
Maybe something like

[D241] = (Round([D237] - [D239], 10) = 0) + 2
 
J

JeffP

Dana suggested a rounding problem and I was skeptical but...even thoug
I couldn't get the code to work, I went to the two cells and used th
Round function and then.....
Bob's code worked exactly as he wrote it (without .text or .valu
properties)

as well as Tim and Smiley's.

Thanks for all the help and teaching.
jeffp :
 
Top