Excel VBA Type mismatch Run-time error 13

Question

With this code

Sub Rondom()

   Dim arr() As Double
   n = InputBox("enter your number")

   ReDim arr(1 To n) As Double
   For i = 1 To n

   arr(i) = Application.WorksheetFunction.NormSInv(Rnd()) * 0.5

   Next i
   cells(3, 4) = Application.WorksheetFunction.Percentile(arr, 0.01)

End Sub

when I enter 60000 for n it works fine. But when I enter 70000, I receive an error saying "type mismatch" and this line

cells(3, 4) = Application.WorksheetFunction.Percentile(arr, 0.01)

is highlighted.

I'm guessing that the Percentile function is expecting a Variant array with 16-bit dimensions and that it's failing trying to do the implicit conversion. (The upper limit of unsigned 16-bits is 65535). — RBarryYoung
– RBarryYoung, Commented Jan 1, 2014 at 16:57

Gary's Student · Accepted Answer · 2014-01-01 17:50:49Z

1

=PERCENTILE() can contain at most 8191 data elements.

answered Jan 1, 2014 at 17:50

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel VBA Type mismatch Run-time error 13

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related