Geek Answers Handbook

VBA application.match 2015 error

In my procedure Main I want to write fast if-statement, which checks whether a valid user input made (user selects the project number from the list of data. See the attached screenshot). To do this, I check if the project number is not part of the project list. If so, an error message is displayed; if not, several other procedures are called.

For some reason, although I get the 2015 error when I run it, this means that the if statement is always right, even with the correct user entries. Can someone help me understand the error please?

  • Entering the project number is a named cell called "IdSelect" and is located on a sheet called "Invoice"
  • The data on which this input is set is on a sheet named "Input"
  • The data is stored in column B and is called the "ProjectList"

The code is below (note: I tried pasting it 5 times, but formatting still doesn’t work this time for any reason - any idea what this might be? The code is formatted correctly. Sorry for the messy display; I can say that this the problem could be very grateful!)

 Sub Main() 'Turn off screen updating Application.ScreenUpdating = False 'Define variable for currently active cell to reactivate it afterwards Dim OldActiveSheet As Object Dim OldActiveCell As Object Dim i As Integer Dim ProjectList As Range Set OldActiveSheet = ActiveSheet Set OldActiveCell = ActiveCell 'If-statement to check whether project number is valid or not Worksheets("Invoice").Activate 'Print to Immediate Window to check value - remove later Debug.Print Range("IdSelect").Value If IsError(Application.Match(Range("IdSelect").Value, "ProjectList", 0)) Then 'Print to Immediate Window to check value - remove later Debug.Print Application.Match(Range("IdSelect").Value, Worksheets("Input").Range("ProjectList"), 0) MsgBox "Invalid Choice: Project with this number does not exist!" Exit Sub Else 'Call procedures to execute Call SortData Call Count_Line_Items Call Count_Total_Rows Call Write_Services(ServCnt) Call Write_Expenses(ExpCnt) End If 'Reactivate previous active cell OldActiveSheet.Activate OldActiveCell.Activate End Sub

Screenshot from the "Login" sheet:

enter image description here

+2
source share
1 answer

The way you relate to a range is rather strange .. because you missed the range link. Oddly enough, you do it right on the next line in Debug.Print Application.Match(Range("IdSelect").Value, Worksheets("Input").Range("ProjectList"), 0)

So try this, please: (I need 100 years to format my own post on a mobile phone .....). Be sure to use a clear link , as shown in my sample code below. Set your sheets accordingly.

 Dim ws as Worksheet Set ws = Sheets(1) IsError(Application.Match(ws.Range("IdSelect").Value, ws.Range("ProjectList"), 0)) Then

And here is for you to read error handling on match .

+7
source

More articles:


All Articles