Min Function on 2D Array

E

ExcelMonkey

Can't seem to get this to work. I am trying to calculate the min value for
the second column in an array. The printout from my Immediate Window tells
me this value should be 2. However the following line returns a value of 1:

MinRow = Application.WorksheetFunction.Min(Array1, 2)

Sub MakeSenseOfArrays()
Dim Array1 As Variant
Dim MinRow As Integer

ReDim Array1(0 To 1, 0 To 2)
Array1(0, 0) = 1
Array1(0, 1) = 2
Array1(0, 2) = 3
Array1(1, 0) = 10
Array1(1, 1) = 20
Array1(1, 2) = 30

For X = 0 To UBound(Array1, 1)
Debug.Print Array1(X, 0) & " " & Array1(X, 1) & " " & Array1(X, 2)
Next

MinRow = Application.WorksheetFunction.Min(Array1, 2)

End Sub


Immediate Window:
1 2 3
10 20 30
 
C

crferguson

Can't seem to get this to work. I am trying to calculate the min value for
the second column in an array. The printout from my Immediate Window tells
me this value should be 2. However the following line returns a value of 1:

MinRow = Application.WorksheetFunction.Min(Array1, 2)

Sub MakeSenseOfArrays()
Dim Array1 As Variant
Dim MinRow As Integer

ReDim Array1(0 To 1, 0 To 2)
Array1(0, 0) = 1
Array1(0, 1) = 2
Array1(0, 2) = 3
Array1(1, 0) = 10
Array1(1, 1) = 20
Array1(1, 2) = 30

For X = 0 To UBound(Array1, 1)
Debug.Print Array1(X, 0) & " " & Array1(X, 1) & " " & Array1(X, 2)
Next

MinRow = Application.WorksheetFunction.Min(Array1, 2)

End Sub

Immediate Window:
1 2 3
10 20 30
Click to expand...

Is 1 not the minimum value of the second column? Am I missing
something? 2 is not the minimum of either column.
 
C

crferguson

Can't seem to get this to work. I am trying to calculate the min value for
the second column in an array. The printout from my Immediate Window tells
me this value should be 2. However the following line returns a value of 1:

MinRow = Application.WorksheetFunction.Min(Array1, 2)

Sub MakeSenseOfArrays()
Dim Array1 As Variant
Dim MinRow As Integer

ReDim Array1(0 To 1, 0 To 2)
Array1(0, 0) = 1
Array1(0, 1) = 2
Array1(0, 2) = 3
Array1(1, 0) = 10
Array1(1, 1) = 20
Array1(1, 2) = 30

For X = 0 To UBound(Array1, 1)
Debug.Print Array1(X, 0) & " " & Array1(X, 1) & " " & Array1(X, 2)
Next

MinRow = Application.WorksheetFunction.Min(Array1, 2)

End Sub

Immediate Window:
1 2 3
10 20 30
Click to expand...

Oh wait, I think I get what's happening here. The min function used
in the manner you've used it in will return 1 because there's a value
in the array less than 2 (the second argument in the min function).
If you were to change the 1 to 100 when filling the array with values,
the min function would return 2. Basically it's finding the lowest
value in the array and using that for the 1st argument of the min
function. Then it's comparing it to the 2nd argument in the min
function (2) and telling you which one of the two arguments is less.

I'd recomment just looping through the second column to find out the
lowest value in it.
 
A

Alan Beban

ExcelMonkey said:
Can't seem to get this to work. I am trying to calculate the min value for
the second column in an array. The printout from my Immediate Window tells
me this value should be 2. However the following line returns a value of 1:

MinRow = Application.WorksheetFunction.Min(Array1, 2)

Sub MakeSenseOfArrays()
Dim Array1 As Variant
Dim MinRow As Integer

ReDim Array1(0 To 1, 0 To 2)
Array1(0, 0) = 1
Array1(0, 1) = 2
Array1(0, 2) = 3
Array1(1, 0) = 10
Array1(1, 1) = 20
Array1(1, 2) = 30

For X = 0 To UBound(Array1, 1)
Debug.Print Array1(X, 0) & " " & Array1(X, 1) & " " & Array1(X, 2)
Next

MinRow = Application.WorksheetFunction.Min(Array1, 2)

End Sub


Immediate Window:
1 2 3
10 20 30
Click to expand...

MinRow = Application.WorksheetFunction.Min(Index(Array1,,2))

Alan Beban
 
E

ExcelMonkey

Allan, I cannot get this line to work:

MinRow = Application.WorksheetFunction.Min(Array1, 2)

I keep getting a "Sub of Function not defined" error.

I event tried the following and recieved an "Arguement not optional" error

MinRow =
Application.WorksheetFunction.Min(Application.WorksheetFunction.Index(Array1,
, 2))

????

EM
 
C

Chip Pearson

How have you declared and loaded the Array1 variable? The following example
code works as expected.

Dim Arr(1 To 3, 1 To 2) As Double
Dim Min As Variant

Arr(1, 1) = 3
Arr(1, 2) = 5
Arr(2, 1) = 7
Arr(2, 2) = 9
Arr(3, 1) = 11
Arr(3, 2) = 12

Min = Application.WorksheetFunction.Min(Arr, 2)
If IsError(Min) = True Then
Debug.Print "Error: " & CStr(Min)
Else
Debug.Print "Min: " & CStr(Min)
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)
 
E

ExcelMonkey

Sub MakeSenseOfArrays()
Dim Array1 As Variant
Dim MinRow As Integer

ReDim Array1(0 To 1, 0 To 2)
Array1(0, 0) = 1
Array1(0, 1) = 2
Array1(0, 2) = 3
Array1(1, 0) = 10
Array1(1, 1) = 20
Array1(1, 2) = 30

For X = 0 To UBound(Array1, 1)
'Print Rows of fist column
Debug.Print Array1(X, 0) & " " & Array1(X, 1) & " " & Array1(X, 2)
Next

MinRow = Application.WorksheetFunction.Min(Index(Array1, , 2))

End Sub
 
C

Chip Pearson

The code works as expected for me once I declared the variable X and change
"Index" to "Application.Index".


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)
 
A

Alan Beban

ExcelMonkey said:
Allan, I cannot get this line to work:

MinRow = Application.WorksheetFunction.Min(Array1, 2)

I keep getting a "Sub of Function not defined" error.

I event tried the following and recieved an "Arguement not optional" error

MinRow =
Application.WorksheetFunction.Min(Application.WorksheetFunction.Index(Array1,
, 2))
Click to expand...

Application.WorksheetFunction.Min(Application.Index(Array1,,2)

Sorry, I left out the second "Application"; it doesn't like

Application.WorksheetFunction.Index(...)

Alan Beban
 
A

Alan Beban

ExcelMonkey said:
Allan, I cannot get this line to work:

MinRow = Application.WorksheetFunction.Min(Array1, 2)

I keep getting a "Sub of Function not defined" error.

I event tried the following and recieved an "Arguement not optional" error

MinRow =
Application.WorksheetFunction.Min(Application.WorksheetFunction.Index(Array1,
, 2))
Click to expand...

As I said in my last post

Application.WorksheetFunction.Min(Application.Index(Array1,,2))

It also works with

Application.WorksheetFunction.Min(Application.WorksheetFunction(Array1,0,2))

Alan Beban

I believe that

Application.WorksheetFunction.Min(Array1, 2)
returns the minimum of all the elements in Array1 and 2; i.e., if Array1
is {1,6,7;8,4,3}, then it returns the minimum of 1,6,7,8,4,3 and 2.

Alan Beban
 
E

ExcelMonkey

Thanks Alan.

Alan Beban said:
Application.WorksheetFunction.Min(Application.Index(Array1,,2)

Sorry, I left out the second "Application"; it doesn't like

Application.WorksheetFunction.Index(...)

Alan Beban
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Make sense of Arrays after Transpose 5
Populating 2-D array w/ Application.[Function] results 1
Array assignment 1
Return values from multidimentsional array 4
"Out of Memory" error 4
Comparing Different Sized Arrays 3
Compare two strings and find longest common sub-string 12
Compare and update arrays 6

Top