CODEWITHSUNDEEP
excelvbasortingmatchworksheet-function
CSAW
CSAW

Reputation: 114

Excel VBA: "unable to get the match property of the worksheetfunction class" Error when match should exist

So, if hypothetically the Match function was working properly in my situation, the index that the function was searching for exists and should show up. The problem is that it doesn't. Looking at some other posts of people who were having Match problems, it seems as if there is some limitation with it.

Im essentially using the Match function to determine the order of newly sorted lists e.g. numsArray provided a list of 4. I need two strings: 1st containing the two smallest vals, 2nd containing two largest. However, I need the two strings to order their values in terms of lowest to highest index value of numsArray. The Array_Values contain the sorted array, but the numsArray contains the indexes of the values to be ordered.

Anyways,

Provided this code: 

Dim numsArray() As Double 
Dim Array_Values() As Double
Dim high() As Double
Dim low() As Double 
ReDim high(1) 
ReDim low(1)
ReDim numsArray(3)
ReDim Array_Values(3)

numsArray = (37669.1, 37343.6, 24, 16)
Array_Values = ( 16, 24, 37669.1, 37343.6) ' in reality Array_Values = SortFunction(numsArray)

'collect low()
    Dim tempI1
    Dim tempI2
    tempI1 = WorksheetFunction.Match(Array_Values(0), numsArray) '***Error Occurs here***
    tempI2 = WorksheetFunction.Match(Array_Values(1), numsArray)
    low(0) = numsArray(WorksheetFunction.Max(tempI1, tempI2))
    low(1) = numsArray(WorksheetFunction.Min(tempI1, tempI2))

'collect high()
    tempI1 = WorksheetFunction.Match(Array_Values(2), numsArray)
    tempI2 = WorksheetFunction.Match(Array_Values(3), numsArray)
    high(0) = numsArray(WorksheetFunction.Max(tempI1, tempI2))
    high(1) = numsArray(WorksheetFunction.Min(tempI1, tempI2))

How do I get it to work? I would best prefer if I could still use the Match function

Upvotes: 0

Views: 1493

Answers (1)

David Zemens
David Zemens

Reputation: 53663

The Application.Match should work instead of the WorksheetFunction. 

Sub d()
numsArray = Split("37669.1,37343.6,24,16", ",")
Array_Values = Split("16,24,37669.1,37343.6", ",") ' in reality Array_Values = SortFunction(numsArray)

'collect low()
    Dim tempI1
    Dim tempI2
    tempI1 = Application.Match(Array_Values(0), numsArray)
End Sub

However, do note that iteration over the array is up to 10x faster:

Sub d()
numsArray = Split("37669.1,37343.6,24,16", ",")
Array_Values = Split("16,24,37669.1,37343.6", ",") ' in reality Array_Values = SortFunction(numsArray)

'collect low()
    Dim tempI1
    Dim tempI2
    tempI1 = ArrayIndex(numsArray, Array_Values(0))
End Sub
Function ArrayIndex(arr, val) as Variant
Dim i as Long
For i = lBound(arr) to uBound(arr)
    If array(i) = val then
        Arrayindex = i
        GoTO EarlyExit
    End If
Next
ArrayIndex = CVerr(2042) 'value NOT found
EarlyExit:
End Sub

Upvotes: 1

Related Questions