Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Does Not Work
Greetings,
I am trying to add two numbers together but I can't get it to give the right answer. Where T8 = $100.00 and T9 = $ 8.00. Both T8 and T9 are TextBoxes as is T10. Here are a couple of the variations of code that I tried: T10.Text = Val(T8.Text) + Val(T9.Text) returns 0 T10.Text = Val(T8.Text) + T9.Text returns 8 T10.Text = T8.Text + Val(T9.Text) returns 100 T10.Text = T8.Text + T9.Text T10.Text = T8.Value + T9.Text T10.Text = T8.Text + T9.Value T10.Text = T8.Value + T9.Value Each returns $ 100.00$ 8.00 Does anyone see what is wrong? Any help would be appreciated. TIA -Minitman |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Does Not Work
Hi
not tested but try: T10.Text = CDbl(T8.Text) + CDbl(T9.Text) -- Regards Frank Kabel Frankfurt, Germany Minitman wrote: Greetings, I am trying to add two numbers together but I can't get it to give the right answer. Where T8 = $100.00 and T9 = $ 8.00. Both T8 and T9 are TextBoxes as is T10. Here are a couple of the variations of code that I tried: T10.Text = Val(T8.Text) + Val(T9.Text) returns 0 T10.Text = Val(T8.Text) + T9.Text returns 8 T10.Text = T8.Text + Val(T9.Text) returns 100 T10.Text = T8.Text + T9.Text T10.Text = T8.Value + T9.Text T10.Text = T8.Text + T9.Value T10.Text = T8.Value + T9.Value Each returns $ 100.00$ 8.00 Does anyone see what is wrong? Any help would be appreciated. TIA -Minitman |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Does Not Work
Convert the text to an numerical data type such as Decimal
for exmpl: Dim totl As Variant totl = CDec(TextBox1.Value) + CDec(TextBox2.Value) HTH Devin -----Original Message----- Greetings, I am trying to add two numbers together but I can't get it to give the right answer. Where T8 = $100.00 and T9 = $ 8.00. Both T8 and T9 are TextBoxes as is T10. Here are a couple of the variations of code that I tried: T10.Text = Val(T8.Text) + Val(T9.Text) returns 0 T10.Text = Val(T8.Text) + T9.Text returns 8 T10.Text = T8.Text + Val(T9.Text) returns 100 T10.Text = T8.Text + T9.Text T10.Text = T8.Value + T9.Text T10.Text = T8.Text + T9.Value T10.Text = T8.Value + T9.Value Each returns $ 100.00$ 8.00 Does anyone see what is wrong? Any help would be appreciated. TIA -Minitman . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Does Not Work
Hi
This example worked fine for me on a userform. TextBox3.Value = Format(Val(TextBox1.Value) + Val(TextBox2.Value), "$###.00") TextBox1.Value = Format(Val(TextBox1.Value), "$###.00") TextBox2.Value = Format(Val(TextBox2.Value), "$###.00") HTH Ken |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Does Not Work
Hey Ken,
Thanks for the reply, however it didn't work for me. As written (Names changes to actual), this code overwrote TextBox 1&2 with 0's, leaving TextBox 3 = 0. Not sure what is up with my Excel. -Minitman On Tue, 14 Dec 2004 17:05:36 -0500, "Ken Macksey" wrote: Hi This example worked fine for me on a userform. TextBox3.Value = Format(Val(TextBox1.Value) + Val(TextBox2.Value), "$###.00") TextBox1.Value = Format(Val(TextBox1.Value), "$###.00") TextBox2.Value = Format(Val(TextBox2.Value), "$###.00") HTH Ken |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Does Not Work
Hey Devin,
The CDec() worked like a charm as did the CDbl()(from Frank's reply). Thank you. -Minitman On Tue, 14 Dec 2004 13:57:49 -0800, "DMoney" wrote: Convert the text to an numerical data type such as Decimal for exmpl: Dim totl As Variant totl = CDec(TextBox1.Value) + CDec(TextBox2.Value) HTH Devin -----Original Message----- Greetings, I am trying to add two numbers together but I can't get it to give the right answer. Where T8 = $100.00 and T9 = $ 8.00. Both T8 and T9 are TextBoxes as is T10. Here are a couple of the variations of code that I tried: T10.Text = Val(T8.Text) + Val(T9.Text) returns 0 T10.Text = Val(T8.Text) + T9.Text returns 8 T10.Text = T8.Text + Val(T9.Text) returns 100 T10.Text = T8.Text + T9.Text T10.Text = T8.Value + T9.Text T10.Text = T8.Text + T9.Value T10.Text = T8.Value + T9.Value Each returns $ 100.00$ 8.00 Does anyone see what is wrong? Any help would be appreciated. TIA -Minitman . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Does Not Work
Hey Frank,
Thanks for the response Both you and Devin came up with similar solutions, and they both work very well, [CDec() and CDbl()], I can not see any advantage of using one over the other, is there one? Thank you. -Minitman On Tue, 14 Dec 2004 22:54:26 +0100, "Frank Kabel" wrote: Hi not tested but try: T10.Text = CDbl(T8.Text) + CDbl(T9.Text) -- Regards Frank Kabel Frankfurt, Germany Minitman wrote: Greetings, I am trying to add two numbers together but I can't get it to give the right answer. Where T8 = $100.00 and T9 = $ 8.00. Both T8 and T9 are TextBoxes as is T10. Here are a couple of the variations of code that I tried: T10.Text = Val(T8.Text) + Val(T9.Text) returns 0 T10.Text = Val(T8.Text) + T9.Text returns 8 T10.Text = T8.Text + Val(T9.Text) returns 100 T10.Text = T8.Text + T9.Text T10.Text = T8.Value + T9.Text T10.Text = T8.Text + T9.Value T10.Text = T8.Value + T9.Value Each returns $ 100.00$ 8.00 Does anyone see what is wrong? Any help would be appreciated. TIA -Minitman |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Does Not Work
Here is why
? val("$8.00") 0 Val stops evaluating at the first non-numeric character. That is why it didn't work "right" with 8.00%. However, cdbl will accept and ignore the $, but will cause an error with "8.00%" Using cdec is overkill. It uses up 14 bytes to store a value and is designed for huge numbers. Double (8 bytes) or single (4 bytes) is more realistic: cdbl or csng -- Regards, Tom Ogilvy "Minitman" wrote in message ... Hey Devin, The CDec() worked like a charm as did the CDbl()(from Frank's reply). Thank you. -Minitman On Tue, 14 Dec 2004 13:57:49 -0800, "DMoney" wrote: Convert the text to an numerical data type such as Decimal for exmpl: Dim totl As Variant totl = CDec(TextBox1.Value) + CDec(TextBox2.Value) HTH Devin -----Original Message----- Greetings, I am trying to add two numbers together but I can't get it to give the right answer. Where T8 = $100.00 and T9 = $ 8.00. Both T8 and T9 are TextBoxes as is T10. Here are a couple of the variations of code that I tried: T10.Text = Val(T8.Text) + Val(T9.Text) returns 0 T10.Text = Val(T8.Text) + T9.Text returns 8 T10.Text = T8.Text + Val(T9.Text) returns 100 T10.Text = T8.Text + T9.Text T10.Text = T8.Value + T9.Text T10.Text = T8.Text + T9.Value T10.Text = T8.Value + T9.Value Each returns $ 100.00$ 8.00 Does anyone see what is wrong? Any help would be appreciated. TIA -Minitman . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Does Not Work - Solved
Thanks Tom, that clarifies the matter.
-Minitman On Tue, 14 Dec 2004 21:52:03 -0500, "Tom Ogilvy" wrote: Here is why ? val("$8.00") 0 Val stops evaluating at the first non-numeric character. That is why it didn't work "right" with 8.00%. However, cdbl will accept and ignore the $, but will cause an error with "8.00%" Using cdec is overkill. It uses up 14 bytes to store a value and is designed for huge numbers. Double (8 bytes) or single (4 bytes) is more realistic: cdbl or csng |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Does Not Work
Hi
see Tom's reply :-) -- Regards Frank Kabel Frankfurt, Germany Minitman wrote: Hey Frank, Thanks for the response Both you and Devin came up with similar solutions, and they both work very well, [CDec() and CDbl()], I can not see any advantage of using one over the other, is there one? Thank you. -Minitman On Tue, 14 Dec 2004 22:54:26 +0100, "Frank Kabel" wrote: Hi not tested but try: T10.Text = CDbl(T8.Text) + CDbl(T9.Text) -- Regards Frank Kabel Frankfurt, Germany Minitman wrote: Greetings, I am trying to add two numbers together but I can't get it to give the right answer. Where T8 = $100.00 and T9 = $ 8.00. Both T8 and T9 are TextBoxes as is T10. Here are a couple of the variations of code that I tried: T10.Text = Val(T8.Text) + Val(T9.Text) returns 0 T10.Text = Val(T8.Text) + T9.Text returns 8 T10.Text = T8.Text + Val(T9.Text) returns 100 T10.Text = T8.Text + T9.Text T10.Text = T8.Value + T9.Text T10.Text = T8.Text + T9.Value T10.Text = T8.Value + T9.Value Each returns $ 100.00$ 8.00 Does anyone see what is wrong? Any help would be appreciated. TIA -Minitman |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Does Not Work
The + operator is also used in .Net for concatenation. You can use the
Convert class to convert a type to another type as Convert.ToInt32(string) with regards, J.V.Ravichandran - http://www.geocities.com/ jvravichandran - http://www.411asp.net/func/search? qry=Ravichandran+J.V.&cob=aspnetpro - http://www.southasianoutlook.com - http://www.MSDNAA.Net - http://www.csharphelp.com - http://www.poetry.com/Publications/ display.asp?ID=P3966388&BN=999&PN=2 - Or, just search on "J.V.Ravichandran" at http://www.Google.com *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Does Not Work
Hey Tom,
What if instead of a number you have "" sometimes and 0 in others, in the same TextBox. What do you use then. -Minitman On Tue, 14 Dec 2004 21:52:03 -0500, "Tom Ogilvy" wrote: Here is why ? val("$8.00") 0 Val stops evaluating at the first non-numeric character. That is why it didn't work "right" with 8.00%. However, cdbl will accept and ignore the $, but will cause an error with "8.00%" Using cdec is overkill. It uses up 14 bytes to store a value and is designed for huge numbers. Double (8 bytes) or single (4 bytes) is more realistic: cdbl or csng |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Does Not Work
Put in checks for those conditions
if tb8.Value = "" then vVal = 0 elseif tb8.Value = 0 then vVal = 0 elseif isnumeric(tb8.value) then vVal = cdbl(tb8.value) else vVal = 0 End if or probably better: if isnumeric(tb8.Value) and tb8.Value < "" then vVal = cdbl(tb8.Value) else vVal = 0 End if -- Regards, Tom Ogilvy "Minitman" wrote in message ... Hey Tom, What if instead of a number you have "" sometimes and 0 in others, in the same TextBox. What do you use then. -Minitman On Tue, 14 Dec 2004 21:52:03 -0500, "Tom Ogilvy" wrote: Here is why ? val("$8.00") 0 Val stops evaluating at the first non-numeric character. That is why it didn't work "right" with 8.00%. However, cdbl will accept and ignore the $, but will cause an error with "8.00%" Using cdec is overkill. It uses up 14 bytes to store a value and is designed for huge numbers. Double (8 bytes) or single (4 bytes) is more realistic: cdbl or csng |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Does Not Work
Hey Tom,
WOW! I didn't know you could do more then one elseif at a time! That is very good to know - Thanks. As for the reason for this post, both solutions look good. Thank you. -Minitman On Thu, 16 Dec 2004 08:43:38 -0500, "Tom Ogilvy" wrote: Put in checks for those conditions if tb8.Value = "" then vVal = 0 elseif tb8.Value = 0 then vVal = 0 elseif isnumeric(tb8.value) then vVal = cdbl(tb8.value) else vVal = 0 End if or probably better: if isnumeric(tb8.Value) and tb8.Value < "" then vVal = cdbl(tb8.Value) else vVal = 0 End if |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Does Not Work
Hey Tom,
The first time I ran with the four boxes all = "". it ran until it got to box 4. I entered a figure into box 3 (the code formats the boxes on exiting. Boxes 1. 2 and 3 are $ ###0.00 and box 4 is ##0.00 %) and ran it again. It still wanted a figure in box 3 (where there now was one). The code worked on the other 3 boxes when they = "", why not this box? Here is the code for box 4: Dim vVal4 ... If IsNumeric(T4.Value) And T4.Value < "" Then vVal4 = T4.Value Else vVal4 = 0 End If I am confused as to how to make it see that T4 has a valid entry (I tried - vVal4 = CDbl(T4.Value), but got a 'type mismatch' error). I could really use your insight. TIA -Minitman On Thu, 16 Dec 2004 08:35:51 -0600, Minitman wrote: Hey Tom, WOW! I didn't know you could do more then one elseif at a time! That is very good to know - Thanks. As for the reason for this post, both solutions look good. Thank you. -Minitman On Thu, 16 Dec 2004 08:43:38 -0500, "Tom Ogilvy" wrote: Put in checks for those conditions if tb8.Value = "" then vVal = 0 elseif tb8.Value = 0 then vVal = 0 elseif isnumeric(tb8.value) then vVal = cdbl(tb8.value) else vVal = 0 End if or probably better: if isnumeric(tb8.Value) and tb8.Value < "" then vVal = cdbl(tb8.Value) else vVal = 0 End if |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Does Not Work
Correction!
Reads: to box 4. I entered a figure into box 3 (the code formats the boxes Should read: to box 4. I entered a figure into box 4 (the code formats the boxes Sorry for the typo -Minitman On Thu, 16 Dec 2004 09:51:42 -0600, Minitman wrote: Hey Tom, The first time I ran with the four boxes all = "". it ran until it got to box 4. I entered a figure into box 3 (the code formats the boxes on exiting. Boxes 1. 2 and 3 are $ ###0.00 and box 4 is ##0.00 %) and ran it again. It still wanted a figure in box 3 (where there now was one). The code worked on the other 3 boxes when they = "", why not this box? Here is the code for box 4: Dim vVal4 ... If IsNumeric(T4.Value) And T4.Value < "" Then vVal4 = T4.Value Else vVal4 = 0 End If I am confused as to how to make it see that T4 has a valid entry (I tried - vVal4 = CDbl(T4.Value), but got a 'type mismatch' error). I could really use your insight. TIA -Minitman On Thu, 16 Dec 2004 08:35:51 -0600, Minitman wrote: Hey Tom, WOW! I didn't know you could do more then one elseif at a time! That is very good to know - Thanks. As for the reason for this post, both solutions look good. Thank you. -Minitman On Thu, 16 Dec 2004 08:43:38 -0500, "Tom Ogilvy" wrote: Put in checks for those conditions if tb8.Value = "" then vVal = 0 elseif tb8.Value = 0 then vVal = 0 elseif isnumeric(tb8.value) then vVal = cdbl(tb8.value) else vVal = 0 End if or probably better: if isnumeric(tb8.Value) and tb8.Value < "" then vVal = cdbl(tb8.Value) else vVal = 0 End if |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Does Not Work
OPPs! Found another one.
Here is the corrected whole paragraph in it's entirety: The first time I ran with the four boxes all = "". it ran until it got to box 4. I entered a figure into box 4 (the code formats the boxes on exiting. Boxes 1. 2 and 3 are $ ###0.00 and box 4 is ##0.00 %) and ran it again. It still wanted a figure in box 4 (where there now was one). The code worked on the other 3 boxes when they = "", why not this box? Here is the code for box 4: Sorry for the typos. -Minitman On Thu, 16 Dec 2004 09:51:42 -0600, Minitman wrote: Hey Tom, The first time I ran with the four boxes all = "". it ran until it got to box 4. I entered a figure into box 3 (the code formats the boxes on exiting. Boxes 1. 2 and 3 are $ ###0.00 and box 4 is ##0.00 %) and ran it again. It still wanted a figure in box 3 (where there now was one). The code worked on the other 3 boxes when they = "", why not this box? Here is the code for box 4: Dim vVal4 ... If IsNumeric(T4.Value) And T4.Value < "" Then vVal4 = T4.Value Else vVal4 = 0 End If I am confused as to how to make it see that T4 has a valid entry (I tried - vVal4 = CDbl(T4.Value), but got a 'type mismatch' error). I could really use your insight. TIA -Minitman On Thu, 16 Dec 2004 08:35:51 -0600, Minitman wrote: Hey Tom, WOW! I didn't know you could do more then one elseif at a time! That is very good to know - Thanks. As for the reason for this post, both solutions look good. Thank you. -Minitman On Thu, 16 Dec 2004 08:43:38 -0500, "Tom Ogilvy" wrote: Put in checks for those conditions if tb8.Value = "" then vVal = 0 elseif tb8.Value = 0 then vVal = 0 elseif isnumeric(tb8.value) then vVal = cdbl(tb8.value) else vVal = 0 End if or probably better: if isnumeric(tb8.Value) and tb8.Value < "" then vVal = cdbl(tb8.Value) else vVal = 0 End if |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Does Not Work
Hey Tom,
After rereading this I realized that one question remains, if Val and CDbl both have problems with 8.00 %, then what will work? This would seem to apply to my last question. Any help would be appreciated. TIA -Minitman On Tue, 14 Dec 2004 21:52:03 -0500, "Tom Ogilvy" wrote: Here is why ? val("$8.00") 0 Val stops evaluating at the first non-numeric character. That is why it didn't work "right" with 8.00%. However, cdbl will accept and ignore the $, but will cause an error with "8.00%" Using cdec is overkill. It uses up 14 bytes to store a value and is designed for huge numbers. Double (8 bytes) or single (4 bytes) is more realistic: cdbl or csng |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Does Not Work
Remember, Cdbl won't handle the %, and isnumeric doesn't work either.
If Trim(T4.Value) < "" Then vVal4 = 0 Else s = Trim(T4.Value) s = left(s,len(s)-1) vVal = cdbl(s) * 0.01 End If -- Regards, Tom Ogilvy "Minitman" wrote in message ... OPPs! Found another one. Here is the corrected whole paragraph in it's entirety: The first time I ran with the four boxes all = "". it ran until it got to box 4. I entered a figure into box 4 (the code formats the boxes on exiting. Boxes 1. 2 and 3 are $ ###0.00 and box 4 is ##0.00 %) and ran it again. It still wanted a figure in box 4 (where there now was one). The code worked on the other 3 boxes when they = "", why not this box? Here is the code for box 4: Sorry for the typos. -Minitman On Thu, 16 Dec 2004 09:51:42 -0600, Minitman wrote: Hey Tom, The first time I ran with the four boxes all = "". it ran until it got to box 4. I entered a figure into box 3 (the code formats the boxes on exiting. Boxes 1. 2 and 3 are $ ###0.00 and box 4 is ##0.00 %) and ran it again. It still wanted a figure in box 3 (where there now was one). The code worked on the other 3 boxes when they = "", why not this box? Here is the code for box 4: Dim vVal4 ... If IsNumeric(T4.Value) And T4.Value < "" Then vVal4 = T4.Value Else vVal4 = 0 End If I am confused as to how to make it see that T4 has a valid entry (I tried - vVal4 = CDbl(T4.Value), but got a 'type mismatch' error). I could really use your insight. TIA -Minitman On Thu, 16 Dec 2004 08:35:51 -0600, Minitman wrote: Hey Tom, WOW! I didn't know you could do more then one elseif at a time! That is very good to know - Thanks. As for the reason for this post, both solutions look good. Thank you. -Minitman On Thu, 16 Dec 2004 08:43:38 -0500, "Tom Ogilvy" wrote: Put in checks for those conditions if tb8.Value = "" then vVal = 0 elseif tb8.Value = 0 then vVal = 0 elseif isnumeric(tb8.value) then vVal = cdbl(tb8.value) else vVal = 0 End if or probably better: if isnumeric(tb8.Value) and tb8.Value < "" then vVal = cdbl(tb8.Value) else vVal = 0 End if |
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Does Not Work
Val doesn't have a problem with it - it just doesn't utilize the %. The
solution was to multiply it by .01 -- Regards, Tom Ogilvy "Minitman" wrote in message ... Hey Tom, After rereading this I realized that one question remains, if Val and CDbl both have problems with 8.00 %, then what will work? This would seem to apply to my last question. Any help would be appreciated. TIA -Minitman On Tue, 14 Dec 2004 21:52:03 -0500, "Tom Ogilvy" wrote: Here is why ? val("$8.00") 0 Val stops evaluating at the first non-numeric character. That is why it didn't work "right" with 8.00%. However, cdbl will accept and ignore the $, but will cause an error with "8.00%" Using cdec is overkill. It uses up 14 bytes to store a value and is designed for huge numbers. Double (8 bytes) or single (4 bytes) is more realistic: cdbl or csng |
#21
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Does Not Work
Hey Tom,
Thanks for clearing up my confusion. -Minitman On Thu, 16 Dec 2004 12:53:26 -0500, "Tom Ogilvy" wrote: Val doesn't have a problem with it - it just doesn't utilize the %. The solution was to multiply it by .01 |
#22
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Does Not Work
Hey Tom
Once I put a "Not" in front of the Trim(T4.Value) line, It works perfectly, I needed a direction to look at and that you did provide, Thank you. -Minitman On Thu, 16 Dec 2004 12:48:41 -0500, "Tom Ogilvy" wrote: Remember, Cdbl won't handle the %, and isnumeric doesn't work either. If Trim(T4.Value) < "" Then vVal4 = 0 Else s = Trim(T4.Value) s = left(s,len(s)-1) vVal = cdbl(s) * 0.01 End If |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why does this code not work? | Excel Discussion (Misc queries) | |||
Help getting code to work. | Excel Programming | |||
Why my code do not work : - ( | Excel Programming | |||
Why my code do not work : - ( | Excel Programming | |||
Why my code do not work : - ( | Excel Programming |