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())

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())

It is very hard to see what is wrong without seeing the data. Min works fine
in itself even in a VBA created and loaded array.

Can you post an example of the data, in text form, that shows the problem.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"DJMF" <(E-Mail Removed)> wrote in message
news860C8CC-6B11-4051-8ACA-(E-Mail Removed)...
>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())

Greg -

> 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.

This would be *much* more inefficient than calling WorksheetFunction.
Transferring data across the VBA-Excel interface multiple times per cell is
much worse than calling the function once per array.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Greg Wilson" <(E-Mail Removed)> wrote in message
news:3D1BC651-B23D-421A-9EF8-(E-Mail Removed)...
> 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())

You've declared your arrays with no specific data type, so they are by
default variants. Variants could contain strings, numbers, or errors, and
only an array of numbers can have a valid minimum. What types of values do
the arrays contain? You need to check each array element to be certain.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"DJMF" <(E-Mail Removed)> wrote in message
news860C8CC-6B11-4051-8ACA-(E-Mail Removed)...
>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())