Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie IF
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie IF
If Range("D237").Value = Range("D239").Value Then
"Jeff" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie IF
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 Jeff wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie IF
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 -- Message posted from http://www.ExcelForum.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie IF
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) "JeffP " wrote in message ... 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' jeff --- Message posted from http://www.ExcelForum.com/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie IF
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 -----Original Message----- 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) "JeffP " wrote in message ... 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' jeff --- Message posted from http://www.ExcelForum.com/ . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie IF
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) "JeffP" wrote in message ... 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 -----Original Message----- 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) "JeffP " wrote in message ... 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' jeff --- Message posted from http://www.ExcelForum.com/ . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie IF
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 -- Dana DeLouis Using Windows XP & Office XP = = = = = = = = = = = = = = = = = "Bob Phillips" wrote in message ... 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) "JeffP" wrote in message ... 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 -----Original Message----- 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) "JeffP " wrote in message ... 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' jeff --- Message posted from http://www.ExcelForum.com/ . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie IF
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 : -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
newbie | Excel Worksheet Functions | |||
Newbie | Excel Worksheet Functions | |||
Real Newbie newbie question | New Users to Excel | |||
Can you help a NEWBIE please | Excel Discussion (Misc queries) | |||
Newbie | Excel Programming |