locked
Match function error RRS feed

  • Question

  • Hi. can i get some help in understanding this?

    i did a match function

    textbox.value = "Ballcap"

     x = Application.Match(textbox.value, pricews.Columns(1), 0)

    the above works.

    however, the below code does not work even when they mean the same thing.

     x = Application.Match("Ballcap", pricews.Columns(1), 0)     ' pricews= thisworkbook.sheets(Product_PriceList)

    the error type mismatch is shown.

    also, can i check if there is any way to use vlookup function to get a return value from a spreadsheet?

    x = Application.WorksheetFunction.VLookup(Sales.TextBox2.value, pricews, 2, False)

    Thank you

    Thursday, January 3, 2013 3:14 PM

All replies

  •  

    Code with controls depends where the control is located and then where the actual code is located.

    Assume that the TextBox is on a worksheet and the code is in the worksheet's module (ie. when you right click the worksheet tab and select View code.)

    The following example is attached to an ActiveX command button on the worksheet and it works fine. Note that TextBox1 can be used on its own when it is in the worksheets module without referencing the worksheet.

    Private Sub CommandButton1_Click()
        Dim pricews As Worksheet
        Dim x As Long
        TextBox1.Value = "Ballcap"
         Set pricews = Worksheets("Product_PriceList")
        x = Application.Match(TextBox1.Value, pricews.Columns(1), 0)
         MsgBox "First example returns: " & x
         x = Application.Match("Ballcap", pricews.Columns(1), 0)
         MsgBox "Second example returns " & x
    End Sub

     

    The following examples of Vlookup. The first example is using the TextBox2 reference on its own when the code is in the worksheet module and the TextBox2 is on the same worksheet.

    Private Sub CommandButton2_Click()
        Dim Sales As Worksheet
        Dim pricews As Worksheet
        Dim x As Variant
       
        Set Sales = Worksheets("SalesTotals")
        Set pricews = Worksheets("Product_PriceList")
       
        x = Application.WorksheetFunction.VLookup(TextBox2.Value, pricews.Columns("A:B"), 2, False)
        MsgBox "Price lookup is " & x
       
    End Sub

    However, if if the code is in a different module to the worksheets module then the code is like the following. (Tested in a standard module.)

    Sub test()
        Dim Sales As Worksheet
        Dim pricews As Worksheet
        Dim x As Variant
       
        Set Sales = Worksheets("SalesTotals")
        Set pricews = Worksheets("Product_PriceList")
       
        x = Application.WorksheetFunction.VLookup(Sales.OLEObjects("TextBox2").Object.Value, pricews.Columns("A:B"), 2, False)
       
        MsgBox "Price lookup is " & x


    End Sub


    Regards, OssieMac

    Friday, January 4, 2013 12:30 PM
  • On Thu, 3 Jan 2013 15:14:43 +0000, aaronkoh wrote:
     
    > x = Application.Match("Ballcap", pricews.Columns(1), 0)     ' pricews= thisworkbook.sheets(Product_PriceList)
    >
    >the error type mismatch is shown.
     
    You can get that error if "x" is declared as Long or Integer, and the Match function is returning an error
     

    Ron
    Friday, January 4, 2013 1:52 PM
  • On Fri, 4 Jan 2013 12:30:52 +0000, OssieMac wrote:
     
    >Private Sub CommandButton1_Click()
    >    Dim pricews As Worksheet
    >    Dim x As Long
    >    TextBox1.Value = "Ballcap"
    >     Set pricews = Worksheets("Product_PriceList")
    >    x = Application.Match(TextBox1.Value, pricews.Columns(1), 0)
    >     MsgBox "First example returns: " & x
    >     x = Application.Match("Ballcap", pricews.Columns(1), 0)
    >     MsgBox "Second example returns " & x
    >End Sub
     
    I believe that code will result in a type-mismatch run time error if the Match function errors. (At least it does in Excel 2007). I would suggest:
     
    ...
    Dim x as Variant
    ...
    MsgBox "First example returns: " & CStr(x)
    ...
    MsgBox "Second example returns " & CStr(x)
    ...
     
     
     

    Ron
    Friday, January 4, 2013 1:57 PM
  • I believe that code will result in a type-mismatch run time error if the Match function errors. (At least it does in Excel 2007). I would suggest:

    Hello Ron and aaronkoh,

    From Help: "MATCH returns the position of the matched value within lookup_array, not the value itself." Therefore it always returns numeric so I fail to see why a long variable should give a problem. Added with edit: I see the problem if returns an error because not found.

    However, reading your post prompted me to have had another look at it and the Type-mismatch is returned if the value is not found. Need to handle "not found" something like the following.

    Following code edited since initial post. Better coding method

    Private Sub CommandButton1_Click()
        Dim pricews As Worksheet
        Dim x As Variant    'Need variant so that error can be stored
       
        TextBox1.Value = "Ballcap"
       
        Set pricews = Worksheets("Product_PriceList")
       
        x = Application.Match(TextBox1.Value, pricews.Columns(1), 0)
       
        If IsError(x) Then
            MsgBox TextBox1.Value & " not found. " & CStr(x)
        Else
            MsgBox x
        End If
       
    End Sub


    Regards, OssieMac



    • Edited by OssieMac Friday, January 4, 2013 11:20 PM
    Friday, January 4, 2013 9:07 PM
  • Hi OssieMac,

    thank you so much  for the explanation.

    have a clearer idea on the importance to cater for errors.

    Saturday, January 5, 2013 5:56 AM