Point 1:
I would forget all the selecting and use the VBA's Count property instead of
the call to the Excel worksheet function (Count). When VBA makes a call to
Excel to calculate something, which is the case when you resort to calling
worksheet functions, there is a large loss of efficiency. Try this instead. I
have changed the var names to cntA and cntB and added r and r2.

Set r = Range(Cells(9, 1), Cells(9, 1).end(xlDown))
cntA = r.Count
Set r2 = Range(Cells(9, 5), Cells(9, 5).end(xlDown))
cntB = r2.Count

The above is clearer, but alternatively forget r and r2 and just go with:

cntA = Range(Cells(9, 1), Cells(9, 1).end(xlDown)).Count
cntB = Range(Cells(9, 5), Cells(9, 5).end(xlDown)).Count


Point 2:
Granted, I haven't seen all the code, but from what I see, there's no need
for the arrays. You can just read directly from the cell ranges themselves
iterating through each cell and do the data crunching and forget populating
and reading from the arrays.

Point 3:
In your internal loop, you first increment c by 1 (c = c + 1) but then
offset this if it turns out that CombinedRelativeFrequency is not greater
than zero (c = c - 1). Why not just increment c if CombinedRelativeFrequency
is greater than zero. Then you don't have to offset it if it turns out it is
not:
If CombinedRelativeFrequency 0 Then
c = c + 1
A5(c, 1) = CombinedData
A8(c) = CombinedData
A5(c, 2) = CombinedRelativeFrequency
End If

Point 4:
(I substitute MinVal for MinimumValue)
To answer your question, when populating the A8 array inside the inner loop,
I would just compare A8(c) (i.e. each new addition to the array) to the
current value of MinVal. If and only if A8(c) is less than MinVal then change
MinVal to A8(c). After populating A8, MinVal will be the smallest value in
the array. One cunnundrum is that the default value of MinVal is zero. It
needs to be equated to the first value in the array on the first iteration of
the inner loop or it may never be changed from zero:

A8(c) = CombinedData
If c = 1 Then MinVal = A8(c) Else MinVal = IIF(A8(c) < MinVal, A8(c), MinVal)

Just a quick-and-dirty assessment with no testing. Hope it was on track.

Greg


"DJMF" wrote:

I have an array of numbers that I have calculated by multiplying the contents
of two other VBA arrays. I need to calculate the minimum value of the final
calculated array, and I can't get the worksheetfunction.min to work (although
it works for arrays read directly into VBA from a worksheet). According to
the error there is a type mismatch, but no matter what I do with definitions,
I can't get it to work. Here is a cut-out section of the code with the
offending line at the bottom. Thanks

Dim a, b, c, Counta, Countb, MaxData, MinimumValue
Dim CombinedData, CombinedRelativeFrequency


Cells(9, 1).Select
Range(Selection, Selection.End(xlDown)).Select
Counta = Application.WorksheetFunction.Count(Selection)

Cells(9, 5).Select
Range(Selection, Selection.End(xlDown)).Select
Countb = Application.WorksheetFunction.Count(Selection)

Dim A3(1 To 1000, 1 To 2)
Dim A4(1 To 1000, 1 To 2)
Dim A5(1 To 1000000, 1 To 2)
Dim A6(1 To 1000, 1 To 2)
Dim A7(1 To 1000000, 1 To 2)
Dim A8(1 To 1000000)
Dim A9(1 To 1000000, 1 To 2)
Dim A10(1 To 1000000)

c = 0

For a = 1 To Counta

A3(a, 1) = Cells(8 + a, 1).Value
A3(a, 2) = Cells(8 + a, 2).Value

For b = 1 To Countb

A4(b, 1) = Cells(8 + b, 5).Value
A4(b, 2) = Cells(8 + b, 6).Value

c = c + 1

CombinedData = A3(a, 1) * A4(b, 1)
CombinedRelativeFrequency = A3(a, 2) * A4(b, 2)

If CombinedRelativeFrequency 0 Then
A5(c, 1) = CombinedData
A8(c) = CombinedData
A5(c, 2) = CombinedRelativeFrequency
Else
c = c - 1
End If

Next b

Next a

For d = 1 To Countd
MinimumValue = Application.WorksheetFunction.Min(A8())