ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Confused by CDbl behaviour (https://www.excelbanter.com/excel-programming/421817-confused-cdbl-behaviour.html)

Risky Dave
Confused by CDbl behaviour
 
Hi,

Can someone please tell me why this does not work (it throws up a type
mismatch):

Sub Cost_Update()

Dim dMitCost As Double

dMitCost = (CDbl(FmRiskCost.TextBox9.Value) +
CDbl_(FmRiskCost.TextBox10.Value) + CDbl(FmRiskCost.TextBox11.Value) +
CDbl_(FmRiskCost.TextBox12.Value) + CDbl(FmRiskCost.TextBox13.Value))_
FmRiskCost.TextBox23.Value = dMitCost
End

I am trying to capture (up to) five values entered into textboxes on a form,
add them and then send the total value to another text box on the same form.
I can do this by replacing the CDbl with a Val, but the calculation then goes
haywire when lage numbers are inputted.

Is this a variation on the old thing of XL assuming that anything joined by
a '+' sign is a string not a number? If so, how do I get around it?

Hope this makes some sense!

TIA

Dave

Eduardo
Confused by CDbl behaviour
 
Try
If IsNumeric(Me.FmRiskCost.TextBox9.Value) _
And IsNumeric(Me.FmRiskCost.TextBox10.Value) _
And IsNumeric(Me.FmRiskCost.TextBox11.Value) _
And IsNumeric(Me.FmRiskCost.TextBox12.Value) _
And IsNumeric(Me.FmRiskCost.TextBox13.Value) Then

Me.dMitCost.value = (CDbl(FmRiskCost.TextBox9.Value) +
CDbl_(FmRiskCost.TextBox10.Value) + CDbl(FmRiskCost.TextBox11.Value) +
CDbl_(FmRiskCost.TextBox12.Value) + CDbl(FmRiskCost.TextBox13.Value))_
Me.FmRiskCost.TextBox23.Value = Me.dMitCost.Value
End



"Risky Dave" wrote:

Hi,

Can someone please tell me why this does not work (it throws up a type
mismatch):

Sub Cost_Update()

Dim dMitCost As Double

dMitCost = (CDbl(FmRiskCost.TextBox9.Value) +
CDbl_(FmRiskCost.TextBox10.Value) + CDbl(FmRiskCost.TextBox11.Value) +
CDbl_(FmRiskCost.TextBox12.Value) + CDbl(FmRiskCost.TextBox13.Value))_
FmRiskCost.TextBox23.Value = dMitCost
End

I am trying to capture (up to) five values entered into textboxes on a form,
add them and then send the total value to another text box on the same form.
I can do this by replacing the CDbl with a Val, but the calculation then goes
haywire when lage numbers are inputted.

Is this a variation on the old thing of XL assuming that anything joined by
a '+' sign is a string not a number? If so, how do I get around it?

Hope this makes some sense!

TIA

Dave

Risky Dave
Confused by CDbl behaviour
 
Eduardo,

Thanks for the quick response.

I'm not quite sure what you mean with this (I'm not a programmer - just
learnign this stuff as I go along).

If I insert another IF statement around the existing code as per your
posting, XL throws back an error about invalid use of "Me." If I remove the
Me, then the code simply skips past the line intended to capture the input,
ie. the IF statement is false so it is just ignored so I don't get the output
I need.

Obviously, I'm missing something here.

TIA

Dave

"Eduardo" wrote:

Try
If IsNumeric(Me.FmRiskCost.TextBox9.Value) _
And IsNumeric(Me.FmRiskCost.TextBox10.Value) _
And IsNumeric(Me.FmRiskCost.TextBox11.Value) _
And IsNumeric(Me.FmRiskCost.TextBox12.Value) _
And IsNumeric(Me.FmRiskCost.TextBox13.Value) Then

Me.dMitCost.value = (CDbl(FmRiskCost.TextBox9.Value) +
CDbl_(FmRiskCost.TextBox10.Value) + CDbl(FmRiskCost.TextBox11.Value) +
CDbl_(FmRiskCost.TextBox12.Value) + CDbl(FmRiskCost.TextBox13.Value))_
Me.FmRiskCost.TextBox23.Value = Me.dMitCost.Value
End



"Risky Dave" wrote:

Hi,

Can someone please tell me why this does not work (it throws up a type
mismatch):

Sub Cost_Update()

Dim dMitCost As Double

dMitCost = (CDbl(FmRiskCost.TextBox9.Value) +
CDbl_(FmRiskCost.TextBox10.Value) + CDbl(FmRiskCost.TextBox11.Value) +
CDbl_(FmRiskCost.TextBox12.Value) + CDbl(FmRiskCost.TextBox13.Value))_
FmRiskCost.TextBox23.Value = dMitCost
End

I am trying to capture (up to) five values entered into textboxes on a form,
add them and then send the total value to another text box on the same form.
I can do this by replacing the CDbl with a Val, but the calculation then goes
haywire when lage numbers are inputted.

Is this a variation on the old thing of XL assuming that anything joined by
a '+' sign is a string not a number? If so, how do I get around it?

Hope this makes some sense!

TIA

Dave

Eduardo
Confused by CDbl behaviour
 
Hi Dave,
what version of excel are you using

"Risky Dave" wrote:

Eduardo,

Thanks for the quick response.

I'm not quite sure what you mean with this (I'm not a programmer - just
learnign this stuff as I go along).

If I insert another IF statement around the existing code as per your
posting, XL throws back an error about invalid use of "Me." If I remove the
Me, then the code simply skips past the line intended to capture the input,
ie. the IF statement is false so it is just ignored so I don't get the output
I need.

Obviously, I'm missing something here.

TIA

Dave

"Eduardo" wrote:

Try
If IsNumeric(Me.FmRiskCost.TextBox9.Value) _
And IsNumeric(Me.FmRiskCost.TextBox10.Value) _
And IsNumeric(Me.FmRiskCost.TextBox11.Value) _
And IsNumeric(Me.FmRiskCost.TextBox12.Value) _
And IsNumeric(Me.FmRiskCost.TextBox13.Value) Then

Me.dMitCost.value = (CDbl(FmRiskCost.TextBox9.Value) +
CDbl_(FmRiskCost.TextBox10.Value) + CDbl(FmRiskCost.TextBox11.Value) +
CDbl_(FmRiskCost.TextBox12.Value) + CDbl(FmRiskCost.TextBox13.Value))_
Me.FmRiskCost.TextBox23.Value = Me.dMitCost.Value
End



"Risky Dave" wrote:

Hi,

Can someone please tell me why this does not work (it throws up a type
mismatch):

Sub Cost_Update()

Dim dMitCost As Double

dMitCost = (CDbl(FmRiskCost.TextBox9.Value) +
CDbl_(FmRiskCost.TextBox10.Value) + CDbl(FmRiskCost.TextBox11.Value) +
CDbl_(FmRiskCost.TextBox12.Value) + CDbl(FmRiskCost.TextBox13.Value))_
FmRiskCost.TextBox23.Value = dMitCost
End

I am trying to capture (up to) five values entered into textboxes on a form,
add them and then send the total value to another text box on the same form.
I can do this by replacing the CDbl with a Val, but the calculation then goes
haywire when lage numbers are inputted.

Is this a variation on the old thing of XL assuming that anything joined by
a '+' sign is a string not a number? If so, how do I get around it?

Hope this makes some sense!

TIA

Dave

Risky Dave
Confused by CDbl behaviour
 
Office '07 under Vista

"Eduardo" wrote:

Hi Dave,
what version of excel are you using

"Risky Dave" wrote:

Eduardo,

Thanks for the quick response.

I'm not quite sure what you mean with this (I'm not a programmer - just
learnign this stuff as I go along).

If I insert another IF statement around the existing code as per your
posting, XL throws back an error about invalid use of "Me." If I remove the
Me, then the code simply skips past the line intended to capture the input,
ie. the IF statement is false so it is just ignored so I don't get the output
I need.

Obviously, I'm missing something here.

TIA

Dave

"Eduardo" wrote:

Try
If IsNumeric(Me.FmRiskCost.TextBox9.Value) _
And IsNumeric(Me.FmRiskCost.TextBox10.Value) _
And IsNumeric(Me.FmRiskCost.TextBox11.Value) _
And IsNumeric(Me.FmRiskCost.TextBox12.Value) _
And IsNumeric(Me.FmRiskCost.TextBox13.Value) Then

Me.dMitCost.value = (CDbl(FmRiskCost.TextBox9.Value) +
CDbl_(FmRiskCost.TextBox10.Value) + CDbl(FmRiskCost.TextBox11.Value) +
CDbl_(FmRiskCost.TextBox12.Value) + CDbl(FmRiskCost.TextBox13.Value))_
Me.FmRiskCost.TextBox23.Value = Me.dMitCost.Value
End



"Risky Dave" wrote:

Hi,

Can someone please tell me why this does not work (it throws up a type
mismatch):

Sub Cost_Update()

Dim dMitCost As Double

dMitCost = (CDbl(FmRiskCost.TextBox9.Value) +
CDbl_(FmRiskCost.TextBox10.Value) + CDbl(FmRiskCost.TextBox11.Value) +
CDbl_(FmRiskCost.TextBox12.Value) + CDbl(FmRiskCost.TextBox13.Value))_
FmRiskCost.TextBox23.Value = dMitCost
End

I am trying to capture (up to) five values entered into textboxes on a form,
add them and then send the total value to another text box on the same form.
I can do this by replacing the CDbl with a Val, but the calculation then goes
haywire when lage numbers are inputted.

Is this a variation on the old thing of XL assuming that anything joined by
a '+' sign is a string not a number? If so, how do I get around it?

Hope this makes some sense!

TIA

Dave

Eduardo
Confused by CDbl behaviour
 
Hi Dave,
I am not an expert as well but some nice people has helped me a lot and the
code is working beautifully to me, there is only one difference and it is I
don't include the form name and I missed some Me in the totals, sorry, try
this
Try
If IsNumeric(Me.TextBox9.Value) _
And IsNumeric(Me.TextBox10.Value) _
And IsNumeric(Me.TextBox11.Value) _
And IsNumeric(Me.TextBox12.Value) _
And IsNumeric(Me.TextBox13.Value) Then

Me.dMitCost.value = (CDbl(Me.TextBox9.Value) +
CDbl_(Me.TextBox10.Value) + CDbl(Me.TextBox11.Value) +
CDbl_(Me.TextBox12.Value) + CDbl(Me.TextBox13.Value))_
Me.TextBox23.Value = Me.dMitCost.Value
End


"Risky Dave" wrote:

Office '07 under Vista

"Eduardo" wrote:

Hi Dave,
what version of excel are you using

"Risky Dave" wrote:

Eduardo,

Thanks for the quick response.

I'm not quite sure what you mean with this (I'm not a programmer - just
learnign this stuff as I go along).

If I insert another IF statement around the existing code as per your
posting, XL throws back an error about invalid use of "Me." If I remove the
Me, then the code simply skips past the line intended to capture the input,
ie. the IF statement is false so it is just ignored so I don't get the output
I need.

Obviously, I'm missing something here.

TIA

Dave

"Eduardo" wrote:

Try
If IsNumeric(Me.FmRiskCost.TextBox9.Value) _
And IsNumeric(Me.FmRiskCost.TextBox10.Value) _
And IsNumeric(Me.FmRiskCost.TextBox11.Value) _
And IsNumeric(Me.FmRiskCost.TextBox12.Value) _
And IsNumeric(Me.FmRiskCost.TextBox13.Value) Then

Me.dMitCost.value = (CDbl(FmRiskCost.TextBox9.Value) +
CDbl_(FmRiskCost.TextBox10.Value) + CDbl(FmRiskCost.TextBox11.Value) +
CDbl_(FmRiskCost.TextBox12.Value) + CDbl(FmRiskCost.TextBox13.Value))_
Me.FmRiskCost.TextBox23.Value = Me.dMitCost.Value
End



"Risky Dave" wrote:

Hi,

Can someone please tell me why this does not work (it throws up a type
mismatch):

Sub Cost_Update()

Dim dMitCost As Double

dMitCost = (CDbl(FmRiskCost.TextBox9.Value) +
CDbl_(FmRiskCost.TextBox10.Value) + CDbl(FmRiskCost.TextBox11.Value) +
CDbl_(FmRiskCost.TextBox12.Value) + CDbl(FmRiskCost.TextBox13.Value))_
FmRiskCost.TextBox23.Value = dMitCost
End

I am trying to capture (up to) five values entered into textboxes on a form,
add them and then send the total value to another text box on the same form.
I can do this by replacing the CDbl with a Val, but the calculation then goes
haywire when lage numbers are inputted.

Is this a variation on the old thing of XL assuming that anything joined by
a '+' sign is a string not a number? If so, how do I get around it?

Hope this makes some sense!

TIA

Dave

Risky Dave
Confused by CDbl behaviour
 
Eduardo,

Still haven't been able to get it to work so I've just written it as a set
of If statements - really ugly (and inefficient I suspect) but it works

"Eduardo" wrote:

Hi Dave,
I am not an expert as well but some nice people has helped me a lot and the
code is working beautifully to me, there is only one difference and it is I
don't include the form name and I missed some Me in the totals, sorry, try
this
Try
If IsNumeric(Me.TextBox9.Value) _
And IsNumeric(Me.TextBox10.Value) _
And IsNumeric(Me.TextBox11.Value) _
And IsNumeric(Me.TextBox12.Value) _
And IsNumeric(Me.TextBox13.Value) Then

Me.dMitCost.value = (CDbl(Me.TextBox9.Value) +
CDbl_(Me.TextBox10.Value) + CDbl(Me.TextBox11.Value) +
CDbl_(Me.TextBox12.Value) + CDbl(Me.TextBox13.Value))_
Me.TextBox23.Value = Me.dMitCost.Value
End


"Risky Dave" wrote:

Office '07 under Vista

"Eduardo" wrote:

Hi Dave,
what version of excel are you using

"Risky Dave" wrote:

Eduardo,

Thanks for the quick response.

I'm not quite sure what you mean with this (I'm not a programmer - just
learnign this stuff as I go along).

If I insert another IF statement around the existing code as per your
posting, XL throws back an error about invalid use of "Me." If I remove the
Me, then the code simply skips past the line intended to capture the input,
ie. the IF statement is false so it is just ignored so I don't get the output
I need.

Obviously, I'm missing something here.

TIA

Dave

"Eduardo" wrote:

Try
If IsNumeric(Me.FmRiskCost.TextBox9.Value) _
And IsNumeric(Me.FmRiskCost.TextBox10.Value) _
And IsNumeric(Me.FmRiskCost.TextBox11.Value) _
And IsNumeric(Me.FmRiskCost.TextBox12.Value) _
And IsNumeric(Me.FmRiskCost.TextBox13.Value) Then

Me.dMitCost.value = (CDbl(FmRiskCost.TextBox9.Value) +
CDbl_(FmRiskCost.TextBox10.Value) + CDbl(FmRiskCost.TextBox11.Value) +
CDbl_(FmRiskCost.TextBox12.Value) + CDbl(FmRiskCost.TextBox13.Value))_
Me.FmRiskCost.TextBox23.Value = Me.dMitCost.Value
End



"Risky Dave" wrote:

Hi,

Can someone please tell me why this does not work (it throws up a type
mismatch):

Sub Cost_Update()

Dim dMitCost As Double

dMitCost = (CDbl(FmRiskCost.TextBox9.Value) +
CDbl_(FmRiskCost.TextBox10.Value) + CDbl(FmRiskCost.TextBox11.Value) +
CDbl_(FmRiskCost.TextBox12.Value) + CDbl(FmRiskCost.TextBox13.Value))_
FmRiskCost.TextBox23.Value = dMitCost
End

I am trying to capture (up to) five values entered into textboxes on a form,
add them and then send the total value to another text box on the same form.
I can do this by replacing the CDbl with a Val, but the calculation then goes
haywire when lage numbers are inputted.

Is this a variation on the old thing of XL assuming that anything joined by
a '+' sign is a string not a number? If so, how do I get around it?

Hope this makes some sense!

TIA

Dave

Ron Rosenfeld
Confused by CDbl behaviour
 
On Tue, 30 Dec 2008 08:20:05 -0800, Risky Dave
wrote:

Hi,

Can someone please tell me why this does not work (it throws up a type
mismatch):

Sub Cost_Update()

Dim dMitCost As Double

dMitCost = (CDbl(FmRiskCost.TextBox9.Value) +
CDbl_(FmRiskCost.TextBox10.Value) + CDbl(FmRiskCost.TextBox11.Value) +
CDbl_(FmRiskCost.TextBox12.Value) + CDbl(FmRiskCost.TextBox13.Value))_
FmRiskCost.TextBox23.Value = dMitCost
End

I am trying to capture (up to) five values entered into textboxes on a form,
add them and then send the total value to another text box on the same form.
I can do this by replacing the CDbl with a Val, but the calculation then goes
haywire when lage numbers are inputted.

Is this a variation on the old thing of XL assuming that anything joined by
a '+' sign is a string not a number? If so, how do I get around it?

Hope this makes some sense!

TIA

Dave

I would assume that one of the values is text that cannot be coerced to
represent a numeric value.
--ron


All times are GMT +1. The time now is 07:38 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com