application.match "Run-time error '13' Type mismatch"

    I'm building a Rent Sheet. I'm using a UserForm to gather information (i.e. Lot#, Amount Paid, Check#, etc.)
    I would like to use the Lot # as a reference for my table.
    Example: If I enter Lot# 32 and click 'Submit', then the information starts at cell N34 (The starting cell of information for Lot# 32) of my table.


    I'm trying to use "Application.Match"


    When I hit 'Continue' I get "Run-time error '13' Type mismatch"
    I don't know what the mismatch is. My Lot# drop-down references column B (specifically B2:B53) which is on the sheet called Lists.
    The column to which I want to navigate is Data!M:M
    ANY help would be greatly appreciated!


    Code
    Sub Continue_Button_Click() 'Clicking the continue button will do the following: ' Dim lotnum As Integer Lot_List = Application.Match(lotnum, "Data!M:M", 0) ' If Background_CheckBox = True Then Sheets("Data").Range("Data_Start").Offset(1, 8).Value = "65.00" End If  Sheets("Data").Range("Data_Start").Offset(1, 1).Value = Check_MO Sheets("Data").Range("Data_Start").Offset(1, 16).Value = Amount_Paid  Unload Rent_UF  End Sub

    Hi,


    I suggest defining the Lookup Value as a Variant type i.e.


    Code
    Dim lotnum As Variant


    That way it is not dependent on the cell format in the look up array. i.e. suppose the cell format in the lookup array is Text and the Lookup Value is an Integer type you will get an error.


    Also define the Return Variable Lot_List as either a Double or Long Type.


    Regards,


    Tom Rowe...

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login