VBA array mode

[alspoll]

Hi,

I am trying to calculate the mode of an array that is populated based on 8 cells in the spreadsheet.

I have four sets of numbers that are used to fill the array (units, $ for 4 groups)

I am trying to poluate the array to calculate the the $ that has the most units.

For example.

5 units @ $1; 6 units @ $2, 3 units @ $1; 7 units @ $3

The array should then be populated as:

Array(1,1,1,1,1,2,2,2,2,2,2,1,1,1,3,3,3,3,3,3,3,)

Then I need to calculate the mode of this array, which should be 1.

Here is what I have so far:

Option Explicit
Public rangeunits As Range
Public arrdata(), varmode As Integer
Public peunits, iRow, maxunits, dept, k, mfunits, meunits, mwunits, mcunits, mfdollars, medollars, mwdollars, mcdollars As Variant
Sub doitall()
Sheets("pe").Activate
 
iRow = 2
Set rangeunits = Worksheets("pe").Range("Ad2:Ad65000")
maxunits = Application.WorksheetFunction.Max(rangeunits)
dept = Sheets("pe").Range("a" & iRow).Text
Do While dept <> 0
 
'units = Sheets("pe").Range("ad" & iRow).Text
dept = Sheets("pe").Range("a" & iRow).Text
mfunits = Sheets("pe").Range("j" & iRow).Text
meunits = Sheets("pe").Range("p" & iRow).Text
mwunits = Sheets("pe").Range("v" & iRow).Text
mccunits = Sheets("pe").Range("ab" & iRow).Text
mfdollars = Sheets("pe").Range("h" & iRow).Text
medollars = Sheets("pe").Range("n" & iRow).Text
mwdollars = Sheets("pe").Range("t" & iRow).Text
mcdollars = Sheets("pe").Range("z" & iRow).Text
 
If dept = "" Then
Exit Do
End If
'----------------------- initialize array
ReDim arrdata(1 To maxunits)
 
For k = 1 To maxunits
arrdata(k) = 999
Next k
'-----------------------
'----------------------- fill array
For k = 1 To mfunits
arrdata(k) = mcfdollars
Next k
For k = (mfunits + 1) To (mfunits + 1) + meunits
arrdata(k) = mcedollars
Next k
For k = ((mfunits + 1) + meunits) + 1 To (((mfunits + 1) + meunits) + 1) + mwunits
arrdata(k) = mwdollars
Next k
For k = ((((mfunits + 1) + meunits) + 1) + mwunits) + 1 To ((((mfunits + 1) + meunits) + 1) + mwunits + 1) + mcunits
arrdata(k) = mcdollars
Next k
varmode = Application.Mode(arrdata()) I get an error here, type mismatch.
Sheets("pe").Range("an" & iRow).Text = varmode ' suggested retail
 
iRow = iRow + 1
Loop
 
End Sub

PLEASE HELP!!!.

Thank you.

 

[alspoll]

Not sure why, but this was cut off.

This is where I am receiving my error:

varmode = Application.Mode(arrdata()) I get an error here, type mismatch.

Please help.

Al