The msot common in an array

[ShvDK]

I'm creating a dynamic array declared as variant on the fly and then redim preserve it each time I put in a record. The records are all integers

When my array is complete (it has only 1 dimension but thoussands of records) I have to find out which of the records that is the most common within the array.

Anyone who has an idea - I'll prefer to have the 'common record search' in VBA instead of just populating the data in a worksheet!

Thanx in advance!

ShvDK

 

[Richard Schollar]

Hi

Look at Application.WorksheetFunction.Mode - this is untested by the way, but I presume it will work.

 

[VoG]

Hi

Look at Application.WorksheetFunction.Mode - this is untested by the way, but I presume it will work.

It does

Sub test()
Dim myarray(1 To 10), i As Integer
For i = 1 To 10
    myarray(i) = Range("A" & i).Value
Next i
MsgBox WorksheetFunction.Mode(myarray)
End Sub

 

[ShvDK]

Agree.. it seems to work but I get the error msg. 'Invalid procedure call or argument'. Your array is predefined as 1 to 10 - mine is redim'ed and preserved... could that be the reason for the error?

 

[ShvDK]

OOOOuuuuppppsss... sorry guys - I'll forgot that my array was empty due to an error in my code. When I refilled my array the worksheetfunction.mode worked!

Thanx for your assistance!

 

[erik.van.geit]

Hi,

take care, sometimes Mode can not be calculated
try data serie
1, 2, 3, 4, 5, 6, 7, 8, 9, 10

Sub test()
Dim myarray(1 To 10), i As Integer
 
    For i = 1 To 10
    myarray(i) = Range("A" & i).Value
    Next i
 
On Error Resume Next
 
i = WorksheetFunction.Mode(myarray)
    If Err Then
    MsgBox "Mode could not be calculated", vbCritical, "ERROR"
    Else
    'proceed
    End If
 
On Error GoTo 0
 
End Sub

kind regards,
Erik