VBA application.match error 2015 [英] VBA application.match error 2015

问题描述

在我的主要程序中，我想编写一个快速if语句，用于检查用户是否做出了有效的输入（用户从数据列表中选择项目数量，见附件截图）。为此，我正在检查项目号码是否不是项目列表的一部分。如果是，则显示错误信息;如果没有，那么会调用其他一些程序。

由于某些原因，当我运行它时会收到错误2015，这意味着if语句总是为真，即使在正确的用户条目。有人可以帮助我了解错误吗？

• 项目号输入是一个名为IdSelect的单元格，位于一个名为发票


• 此输入被检查的数据位于名为输入的工作表上。


• 数据存储在列B中，称为 ProjectList

下面的代码（注意：我已经尝试粘贴了5次，但由于某种原因，格式化仍然无法运行 - 任何想法可能是什么？代码格式正确对不起，凌乱的显示;如果有人可以告诉我这个问题可能会非常感谢！）

  Sub Main（）
'关闭屏幕更新
 Application.ScreenUpdating = False 
'定义当前活动单元格的变量，然后再次激活
 Dim OldActiveSheet As Object 
 Dim OldActiveCell As Object 
 Dim i As Integer 
 Dim ProjectList As Range 
 Set OldActiveShe et = ActiveSheet 
设置OldActiveCell = ActiveCell 
'If-statement检查项目号是否有效
工作表（发票）。激活
'打印到立即窗口检查值 - 稍后删除
 Debug.Print范围（IdSelect）值
如果IsError（Application.Match（Range（IdSelect）。Value，ProjectList，0））然后
'打印到立即窗口以检查值 - 稍后删除
 Debug.Print Application.Match（Range（IdSelect）。Value，Worksheets（Input）。Range（ProjectList），0）
 MsgBox无效的选择：具有此数字的项目不存在！ 
退出Sub 
 Else 
'调用程序执行$ ​​b $ b调用SortData 
调用Count_Line_Items 
调用Count_Total_Rows 
调用Write_Services（ServCnt）
调用Write_Expenses（ExpCnt）
 End If 
'重新激活以前的活动单元格
 OldActiveSheet.Activate 
 OldActiveCell.Activate 
 End Sub 
 
输入表单中的屏幕快照：
 
 
 
 解决方案

参考范围是相当奇怪的..因为你错过了范围参考。奇怪的是，你在下面一行就可以正确执行
Debug.Print Application.Match（Range（IdSelect）。Value，Worksheets（Input）。Range（ProjectList） ，0）

所以请尝试（请花100年时间在手机上格式化我自己的帖子.....）确保使用显式参考，如下面我的示例代码所示。

  Dim ws as Worksheet 
设置ws =表（1）
 
 IsError（Application.Match（ws.Range（IdSelect）。Value，ws.Range（ProjectList），0））然后
  

这里是为您阅读 匹配上的错误处理

In my Main procedure I want to write a quick if-statement which checks whether the user has made a valid input (user chooses number of project from list of data, see attached screenshot). For that I am checking whether the project number is not part of the list of projects. If that is true, an error message is displayed; if not then a number of other procedures are called.

For some reason though I get error 2015 when I run it, which means that the if-statement is always true, even on correct user entries. Can someone help me understand the error please?

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

Code below (note: I have tried pasting it 5 times but the formatting still won't work this time for some reason - any idea what that could be? The code is properly formatted. Sorry for the messy display; if anyone can tell me what that problem might I would 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 "Input" sheet:

解决方案

The way you refer to range is rather odd.. because you missed out range reference. Oddly enoughbthat you do it correct on the next line at Debug.Print Application.Match(Range("IdSelect").Value, Worksheets("Input").Range("ProjectList"), 0)

So try this please: (it take me 100 years to format my own post on mobile.....). Make sure to use explicit reference 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 on for error handling on on match.

这篇关于VBA application.match error 2015的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！