PDA

View Full Version : Excel VBA - why are cells not equal?



parry
02-07-2002, 06:55 PM
Hi, I have a combobox with a linkedcell value of C5 and a listfillrange of K1:K5 (named as "Term"). In rows 12 down I have IF formulas to look to see if the corresponding cell in column A is equal to the value in cell C5.

The problem is that even when the value of cell C5 (obtained from the combo box) appears equal to the value of cells A12, A13 etc the IF formula results in the 'false' value of the IF formula.

I thought at first maybe cell C5 or the range it was using (K1:K5) had a different category type to cells A12 etc (say Number versus Text) but they are both numbers.

Im stumped, why does Excel not recognise that a value of say 12 in cell C5 is equal to 12 in cell A12? There is nothing wrong with any formulas, & I believe it has something to do with the combobox looking in range K1:K5 and placing the value in C5.

Any suggestions on whats causing this would be greatly appreciated.

wuppo
02-07-2002, 08:14 PM
I'm a little confused by your example: If you have an input range of K1:K5, the value in C5 (cell Link for combo) can have a range of 1 to 5 (being the relative position in the range, of the selected value). Where do you get a value of 12 (in C5)?

To return the value in the combo, you need to use the index function: =index(K1:K5,C1)

parry
02-07-2002, 09:04 PM
Thanks. Yes you are confused - sorry I cant have explained this well enough. I know how to get the value of the combobox into cell C5. The "12" value was just an example of a value in cells K1:K5 (ie whats in the drop down values in the combo) - I could have put any number.

The problem is the number in cell C5 (say 91 - I'll use a different number for an example) is not recognised as 91 by an IF formula referencing C5.

Example
K1:K5 = 91,92,93,94 & 95
C5 = 91 (I selected 91 from the ComboBox dropdown)
A12 = 91 (just the value in the cell)

Now formula in cell B12 says ...
=If(a12=c5,"True","False")
The 'If' formula means if a12 is equal to C5 (which it does because they both have 91 as a value) then return the word 'True' in cell B12. If they arent equal then return the word 'False' in B12.

The trouble is B12 is returning 'False' even though both C5 and A12 (in this example) have 91 as a value. Why?

All cells in this example are formatted the same (as a number).

cheers
Parry

B.M.
02-07-2002, 09:23 PM
The example you gave works perfect for me Parry.

Have you tried it in another workbook?

Cheers

Bob

parry
02-07-2002, 10:39 PM
Thanks, I tried a different workbook but no go. I'm obviously doing something drastically wrong - bugger it :-)

Perhaps I should explain how I created the ComboBox as it must be this thats wrong. First I created the range I wanted for the values in the combobox - K1:K5 (values of 91-95 respectively in these cells) as per example in prev post.

I drew the ComboBox on the sheet (from Toolbox in Visual Basic toolbar) then right clicked it and selected Properties. In here I scrolled down until I found LinkedCell and entered C5 in there (C5 is currently a blank cell) and then in ListFillRange I entered K1:K5. I then closed the Properties window and changed exited from design view to normal view so I could see the ComboBox working.

It appears to work fine - 91 to 95 appear in the box and the value selected gets zapped into cell C5. Just as I wanted - or so I thought :-)

Now if you try the formula as per prev post you will see false comes up even though the same damn value is in both A12 & C5. Go figure?

cheers
Parry.

wuppo
02-07-2002, 11:18 PM
OK, I created the combo from the 'Control Toolbar' not the VB Toolbox - the results are different! (The linked cell in the combo from the control toolbar is an index into the range not the value selected from the range (as it is with the VB combo!).

I think the problem lies in trying to evaluate an equate instead of an expression in the IF function. Try using an expression such as "C5-A12' (in which case the result will be FALSE when zero and TRUE otherwise).

parry
02-07-2002, 11:56 PM
Cheers thanks Wuppo. If I change the formula to =If(a12-c5=0,"True","False") I get True as the result while my previous formula =if(a12=c5,"True","False") comes up False.

Bizarro! Only in the Microsoft world would 91-91=0 work while 91=91 doesnt. Must be the way the water flows down the pipe in a different direction or something in the States :-)

I appreciate your help - another victory for F1. However I dont understand how you get a different combobox result - if I use the VB Toolbar or Control Toolbar I get the same. When I draw the ComboBox the formula bar has =EMBED("Forms.ComboBox.1","") not =INDEX(...). I'm using Excel 2000 by the way in case that matters.

No mind, I'll just put this down to an anomaly (aka feature?) with Excel that I will have to remember.

ta muchly,
Parry

wuppo
03-07-2002, 12:34 AM
Its too late in the day... The combo that produces the 'index' in "C5" came from the Forms toolbar; the combos on the Controls and VB toolbars are the same. There probably is a good reason...

To my understanding, the 'IF' Function performs a logic test on the 'test expression'. If the value of the expression is zero, then False is returned - values other than zero return True.

So an expression such as 'C5-A12' returns a value that can be tested. An equate such as 'C5=A12' does not return a value.

Why 'C5-A12=0' works is anybodys guess, as once again it is an equate rather than an expression. One can only guess that the first part 'C5-12' is evaluated to a boolean which is tested against '0'. Certainly odd - I would tend to keep away from using such an equate where a boolean test is being applied; it may not work everywhere!!

Obviously, 'C5-A12=0' will return the opposite boolean from 'C5-A12'. True or False? :D