CODEWITHSUNDEEP
vbaexcelexcel-2010
Rohan Goel
Rohan Goel

Reputation: 43

Finding text in table using match function

I'm trying to use the match function in Excel using VBA. Its giving me a mismatch error. 

Sub pipe_size()
    Dim x As Single
    Dim y As Single
    Dim NPS As Single
    Dim Sch As String
    Dim z As Single

    NPS = Worksheets("Sheet2").Range("R35").Value
    Sch = Worksheets("Sheet2").Range("R36").Value
    'x is column number, y is row number
    x = Application.Match(NPS, Worksheets("Sheet2").Range("Q5:Q33"), 0)
    'y = Application.Match(Sch, Worksheets("Sheet2").Range("R3:AD3"), 0)

    Worksheets("Sheet2").Range("Y34").Value = Sch
    Worksheets("Sheet2").Range("Y35").Value = x
    'Worksheets("Sheet2").Range("Y36").Value = y
End Sub

NPS is just a number but Sch can be a number or text. Right now, the last 3 lines are just me trying to test if Excel is picking up the right values.

Sch picks up the text and numbers correctly but the line

 y = Application.Match(Sch, Worksheets("Sheet2").Range("R3:AD3"), 0)

is giving me a type mismatch.

Upvotes: 1

Views: 1088

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57673

The issue is that if Sch is numeric and you cast it into a string with

Dim Sch As String
Sch = Worksheets("Sheet2").Range("R36").Value

and then you try to match the string Sch with Range("R3:AD3") this fails because there is no string Sch within the range but only a numeric Sch.

Also you should notice that there is a difference between Application.Match and Application.WorksheetFunction.Match. The first one is a VBA function, the second one is the same as the worksheet function =MATCH() you use in worksheets.

They behave differently:

v = Application.Match(7, Array(1, 2, 3), 0)

Here the Error 2042 gets assigned to the variable v. No error can be caught by VBA.

But this 

v = WorksheetFunction.Match(7, Array(1, 2, 3), 0)

causes a runtime error 1004 that can be caught by VBA like

On Error Resume Next
    v = WorksheetFunction.Match(7, Array(1, 2, 3), 0)
    Debug.Print Err.Number
On Error GoTo 0

Therefore I suggest the following:

  1. Declare NPS and Sch as variant
  2. Always use the WorksheetFunction

So we end up with this …

Sub pipe_size()
    Dim x As Variant, y As Variant
    Dim NPS As Variant, Sch As Variant

    NPS = Worksheets("Sheet2").Range("R35").Value
    Sch = Worksheets("Sheet2").Range("R36").Value

    'x is column number, y is row number
    On Error GoTo errorMatchX
        x = Application.WorksheetFunction.Match(NPS, Worksheets("Sheet2").Range("Q5:Q33"), 0)
    On Error GoTo errorMatchY
        y = Application.WorksheetFunction.Match(Sch, Worksheets("Sheet2").Range("R3:AD3"), 0)
    On Error GoTo 0

    Worksheets("Sheet2").Range("Y34").Value = Sch
    Worksheets("Sheet2").Range("Y35").Value = x
    Worksheets("Sheet2").Range("Y36").Value = y

    Exit Sub

errorMatchX:
    If Err.Number = 1004 Then
        MsgBox "x did not match."
    Else
        MsgBox "Error: " & Err.Number & ": " & Err.Description, vbCritical, Err.Source, Err.HelpFile, Err.HelpContext
    End If
    Exit Sub

errorMatchY:
    If Err.Number = 1004 Then
        MsgBox "y did not match."
    Else
        MsgBox "Error: " & Err.Number & ": " & Err.Description, vbCritical, Err.Source, Err.HelpFile, Err.HelpContext
    End If
End Sub

Upvotes: 2

Related Questions