Hi all:

I'm getting a type mismatch with the following code:

WorksheetFunction.Percentile(rngIn.Columns(Indx), _
CDbl(txtLowPercent.Value) / 100#)

rngIn is type Range and already set. txtLowPercent is a textbox on a
userform - this code in part of the userform code.

If I replace the CDbl() by a number like 95.0 then the code works fine. I
thought CDbl() should work in this situation since Percentile expects a
Double.

So, two questions. First, what's the problem here? Second, is there an
Application level function that replaces the worksheet function Percentile
that I should be using use?

I'm working in Excel 97 under Win98.

Thanks Tony (this is a great group - I wish I had something to offer besides
questions)

Tony,

The only thing I can think of is that the textbox isn't numeric, but
contains letters or such. This would throw that error.

You could make sure only numbers and dot input with this code

Private Sub txtLowPercent_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 48 To 57 'Nos 0 - 9
Exit Sub
Case 32 ' space
Case 46 ' dot
Case Else
KeyAscii = 0
End Select


End Sub


Using worksheetfunctions in VBA is fine, in fact often the best way to avoid
re-inventing the meal.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"George Raft" wrote in message
...
Hi all:

I'm getting a type mismatch with the following code:

WorksheetFunction.Percentile(rngIn.Columns(Indx), _
CDbl(txtLowPercent.Value) / 100#)

rngIn is type Range and already set. txtLowPercent is a textbox on a
userform - this code in part of the userform code.

If I replace the CDbl() by a number like 95.0 then the code works fine. I
thought CDbl() should work in this situation since Percentile expects a
Double.

So, two questions. First, what's the problem here? Second, is there an
Application level function that replaces the worksheet function Percentile
that I should be using use?

I'm working in Excel 97 under Win98.

Thanks Tony (this is a great group - I wish I had something to offer
besides
questions)

Thanks Bob, but now I'm confused.

You're right that txtLowPercent.Value is not numeric - it's a string by
design. It
is set by a spinner using code like CStr(spinner.value) & "%" so that the
user sees a percentage in the text box rather than a decimal value. This is
for aesthetics only, so I could simply enter the numeric value ...

But I thought the type conversion functions were there to handle conversion
from one type to another - shouldn't CDbl() return a double from the
argument it's given? When I print the result, it certainly looks like a
valid number and operations on it (such as /100) yield the expected result.

Tony

Bob Phillips wrote in message
...
Tony,

The only thing I can think of is that the textbox isn't numeric, but
contains letters or such. This would throw that error.

You could make sure only numbers and dot input with this code

Private Sub txtLowPercent_KeyPress(ByVal KeyAscii As
MSForms.ReturnInteger)
Select Case KeyAscii
Case 48 To 57 'Nos 0 - 9
Exit Sub
Case 32 ' space
Case 46 ' dot
Case Else
KeyAscii = 0
End Select


End Sub


Using worksheetfunctions in VBA is fine, in fact often the best way to
avoid
re-inventing the meal.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"George Raft" wrote in message
...
Hi all:

I'm getting a type mismatch with the following code:

WorksheetFunction.Percentile(rngIn.Columns(Indx), _
CDbl(txtLowPercent.Value) / 100#)

rngIn is type Range and already set. txtLowPercent is a textbox on a
userform - this code in part of the userform code.

If I replace the CDbl() by a number like 95.0 then the code works fine.
I
thought CDbl() should work in this situation since Percentile expects a
Double.

So, two questions. First, what's the problem here? Second, is there an
Application level function that replaces the worksheet function
Percentile
that I should be using use?

I'm working in Excel 97 under Win98.

Thanks Tony (this is a great group - I wish I had something to offer
besides
questions)

Tony,

Partially correct, it is just that the CDbl function will fail if it
contains non-numerics, such as a or b (or % :-)). It will convert a numeric
string to a double, but not a character string.

Try putting
?CDbl("a")
in the immediate window and see the problem.
?CDbl("1") is fine though. Then try
?CDbl("1%")

If you have a percentage in the textbox, then try using

CDbl(ReplacetxtLowPercent.Value,"%",""))

I think that will sort you.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"George Raft" wrote in message
...
Thanks Bob, but now I'm confused.

You're right that txtLowPercent.Value is not numeric - it's a string by
design. It
is set by a spinner using code like CStr(spinner.value) & "%" so that the
user sees a percentage in the text box rather than a decimal value. This
is
for aesthetics only, so I could simply enter the numeric value ...

But I thought the type conversion functions were there to handle
conversion
from one type to another - shouldn't CDbl() return a double from the
argument it's given? When I print the result, it certainly looks like a
valid number and operations on it (such as /100) yield the expected
result.

Tony

Bob Phillips wrote in message
...
Tony,

The only thing I can think of is that the textbox isn't numeric, but
contains letters or such. This would throw that error.

You could make sure only numbers and dot input with this code

Private Sub txtLowPercent_KeyPress(ByVal KeyAscii As
MSForms.ReturnInteger)
Select Case KeyAscii
Case 48 To 57 'Nos 0 - 9
Exit Sub
Case 32 ' space
Case 46 ' dot
Case Else
KeyAscii = 0
End Select


End Sub


Using worksheetfunctions in VBA is fine, in fact often the best way to
avoid
re-inventing the meal.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"George Raft" wrote in message
...
Hi all:

I'm getting a type mismatch with the following code:

WorksheetFunction.Percentile(rngIn.Columns(Indx), _
CDbl(txtLowPercent.Value) / 100#)

rngIn is type Range and already set. txtLowPercent is a textbox on a
userform - this code in part of the userform code.

If I replace the CDbl() by a number like 95.0 then the code works
fine.
I
thought CDbl() should work in this situation since Percentile expects
a
Double.

So, two questions. First, what's the problem here? Second, is there
an
Application level function that replaces the worksheet function
Percentile
that I should be using use?

I'm working in Excel 97 under Win98.

Thanks Tony (this is a great group - I wish I had something to offer
besides
questions)