Forum Home
Press F1
Thread ID: 21719	2002-07-02 06:55:00	Excel VBA - why are cells not equal?	parry (27)	Press F1

Post ID	Timestamp	Content	User
59217	2002-07-02 06:55:00	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.	parry (27)
59218	2002-07-02 08:14:00	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)	wuppo (41)
59219	2002-07-02 09:04:00	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	parry (27)
59220	2002-07-02 09:23:00	The example you gave works perfect for me Parry. Have you tried it in another workbook? Cheers Bob	B.M. (505)
59221	2002-07-02 10:39:00	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.	parry (27)
59222	2002-07-02 11:18:00	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).	wuppo (41)
59223	2002-07-02 11:56:00	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	parry (27)
59224	2002-07-02 12:34:00	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	wuppo (41)
1