CODEWITHSUNDEEP
vbaexcelmatch
Mierzen
Mierzen

Reputation: 616

WorksheetFunction.Match not working

Here is an excerpt from my data:

------+------+------+------+------+
      |  A   |  B   |  C   |  D   |
------+------+------+------+------+
1     | 10      20     25     30
2     | 152    181    195    210

and my code:

Dim xrng as range, yrng as range, offset as integer

set xrng = Sheets("Sheet1").Range("A1:D1")
set yrng = Sheets("Sheet1").Range("A2:D2")

offset = WorksheetFunction.Match(23, xrng , 1) - 1

Why does running this result in a 1004 error: Unable to get the match property of the worksheetfunction class? How can I fix it?

EDIT: Detailed problem

Okay, I have written a function that does interpolation:

Public Function interpolate(intvalue_X As Double, xrange As range, yrange As range) As Double
....this is just an excerpt:
    Dim offst As Integer
    offst = WorksheetFunction.Match(intvalue_X, xrange, 1) - 1 'find the offset of the nearest value
---
End Function

With the following data and call, it works fine and returns the correct answer: (don't mind the variables' who's declarations aren't shown - they have been declared at this point, it's just not copied)

enter image description here

Set intXrng = Sheets("Tables").range("B32:G32")
    If beltWidth >= 46 And beltWidth <= 122 And conveyerCenter >= 7.6 And conveyerCenter <= 152.4 Then 'dan kan jy die tabel gebruik
    m = interpolate(beltWidth, intXrng, Sheets("Tables").range("B44:G44"))
    c = interpolate(beltWidth, intXrng, Sheets("Tables").range("B45:G45"))
    powerX = m * conveyerCenter + c
Else
    MsgBox "Unable to use the power x-factor table.", vbCritical
End If

Now, when I use the same function, but with this data and call, it gives the error:

enter image description here

Set intXrng = Sheets("Tables").range("F4:I4")
angleSurcharge = 23
capacityTable = interpolate(angleSurcharge, intXrng, Sheets("Tables").range("F7:I7"))

Upvotes: 0

Views: 3006

Answers (1)

Ripster
Ripster

Reputation: 3585

Your values are not stored as strings because they are in a table header. Table headers are always read as strings regardless of their format.

You can convert all values to the Doubles before passing it to Worksheet.Match to fix the bug.

Dim offst As Integer
Dim arry As Variant
ReDim arry(1 To 1, 1 To xrange.Columns.Count)

For i = 1 To xrange.Columns.Count
    arry(1, i) = CDbl(xrange.Cells(1, i).Value)
Next
offst = WorksheetFunction.Match(intvalue_X, arry, 1) - 1    'find the offset of the nearest value

Upvotes: 1

Related Questions