Finding position of max value VBA array

[RVP]

Hello,

I am looking to find the max or min in each column of a VBA array. I have already calculated the Max/Min and am now looking to find the position of that value within each column. I am getting a syntax error because I do not know how to tell excel to look through all the rows for each column. Below is a the problem in my code. Is there a wildcard or something I can put in place of the row argument?

dim Res (12,10) as Variant
dim pos as Double
dim Min as Double
for m = 1 to 10

pos = WorksheetFunction.Match(Min, Res(Rows, m - 1), 0)

next m

Thanks in advance,

Rob

 

[Jon Peltier]

You have declared but not populated Res and Min, I don't know what Res(Rows, m-1) is, but it doesn't look like an array to me, and I don't know why you're looping over m.

When I run this code I get both a Min and a Position:

Sub FindMin()
  Dim iMin As Double
  Dim iCount As Long
  Dim vValues As Variant
  
  vValues = Array(2, 4, 6, 8, 1, 3, 5, 7, 9)
  
  iMin = WorksheetFunction.Min(vValues)
  
  iCount = WorksheetFunction.Match(iMin, vValues, 0)

End Sub

 

[Kenny1690]

Hey I tried the exact same code that u mentioned. But it gives me an error saying "Unable to get the MAtch property of the Worksheet Class". PLease let me know if u know how to fix this. Thanks

 

[Jon Peltier]

I just copied that code, pasted it into a blank code module in a new workbook in Excel 2013, and it ran without a hitch, finding iMin = 1 and iCount = 5.

Did you do exactly this, or did you modify it somewhat? For example, using MATCH with a third argument of zero, as in my example, will fail if the exact value, the first argument of MATCH, is not present in the reference in the second argument. For example, this will fail with the error message you received:

Sub Findnumber()
  Dim iNumber As Double
  Dim iCount As Long
  Dim vValues As Variant
  
  vValues = Array(2, 4, 6, 8, 1, 3, 5, 7, 9)
  
  iNumber = 1.5
  
  iCount = WorksheetFunction.Match(iNumber, vValues, 0)

End Sub

Instead of an exact match, if the array is sorted in ascending order, change 0 to 1, and MATCH will find the largest value of the array that does not exceed the sought value. In this example:

Sub Findnumber()
  Dim iNumber As Double
  Dim iCount As Long
  Dim vValues As Variant
  
  vValues = Array(1, 1.25, 1.75, 2, 3)
  
  iNumber = 1.5
  
  iCount = WorksheetFunction.Match(iNumber, vValues, 1)

End Sub

iCount returns 2, which is the position of 1.25, the last value less than iNumber.

 

[Kenny1690]

Hey I have an array of time values. Do you think this might be the reason my code isnt working?
Thanks Jon. Appreciate it.

 

[Jon Peltier]

If you have numerical time values, it should work. If you have some kind of string representation of time, it may not.