Min Function on 2D Array
 

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

Min Function on 2D Array
 

On Dec 11, 11:06 am, ExcelMonkey
wrote:
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

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

Min Function on 2D Array
 

On Dec 11, 11:06 am, ExcelMonkey
wrote:
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

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.

Min Function on 2D Array
 

ExcelMonkey wrote:
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

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

Alan Beban

Min Function on 2D Array
 

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.Work sheetFunction.Index(Array1,
, 2))

????

EM


"Alan Beban" wrote:

ExcelMonkey wrote:
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

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

Alan Beban

Min Function on 2D Array
 

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)



"ExcelMonkey" wrote in message
...
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.Work sheetFunction.Index(Array1,
, 2))

????

EM


"Alan Beban" wrote:

ExcelMonkey wrote:
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

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

Alan Beban

Min Function on 2D Array
 

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

"Chip Pearson" wrote:

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)



"ExcelMonkey" wrote in message
...
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.Work sheetFunction.Index(Array1,
, 2))

????

EM


"Alan Beban" wrote:

ExcelMonkey wrote:
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

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

Alan Beban

Min Function on 2D Array
 

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)

"ExcelMonkey" wrote in message
...
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

"Chip Pearson" wrote:

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)



"ExcelMonkey" wrote in message
...
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.Work sheetFunction.Index(Array1,
, 2))

????

EM


"Alan Beban" wrote:

ExcelMonkey wrote:
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

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

Alan Beban

Min Function on 2D Array
 

ExcelMonkey wrote:
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.Work sheetFunction.Index(Array1,
, 2))

Application.WorksheetFunction.Min(Application.Inde x(Array1,,2)

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

Application.WorksheetFunction.Index(...)

Alan Beban

Min Function on 2D Array
 

ExcelMonkey wrote:
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.Work sheetFunction.Index(Array1,
, 2))


As I said in my last post

Application.WorksheetFunction.Min(Application.Inde x(Array1,,2))

It also works with

Application.WorksheetFunction.Min(Application.Work sheetFunction(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

Min Function on 2D Array
 

Thanks Alan.

"Alan Beban" wrote:

ExcelMonkey wrote:
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.Work sheetFunction.Index(Array1,
, 2))

Application.WorksheetFunction.Min(Application.Inde x(Array1,,2)

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

Application.WorksheetFunction.Index(...)

Alan Beban