Vlookup应用程序或对象定义的错误

Question

我想问waht我在这里做错了吗？我希望vlookup查找是否已经存在ProjectID，如果是的，我希望它跳过注册步骤，然后立即转到Next Row_t以查看下一行数据，每当我启动错误"应用程序定义"或对象定义的错误"线上出现：

 Repetition = Application.WorksheetFunction.VLookup(Worksheets(NBG_RegionaDataWorksheetName).Cells(Row_T, ProjectID).Value, Worksheets(Issue_SumofSharesWorksheetName).Range("A2:AS"), 1)

我不知道怎么了，你能帮我吗？

For Row_S = 2 To MAX_Row_S
 SourceYear = Worksheets(NBG_SourceRegionDataWorksheetName).Cells(Row_S, SOP).Value
     SourceYear = DatePart("yyyy", SourceYear)
     SourceCarmaker = Worksheets(NBG_SourceRegionDataWorksheetName).Cells(Row_S, carmaker).Value
     SourceProject = Worksheets(NBG_SourceRegionDataWorksheetName).Cells(Row_S, Project).Value
     SourceFamily = Worksheets(NBG_SourceRegionDataWorksheetName).Cells(Row_S, Family).Value
     SourceStatus = Worksheets(NBG_SourceRegionDataWorksheetName).Cells(Row_S, Status).Value
     SourceShare = Worksheets(NBG_SourceRegionDataWorksheetName).Cells(Row_S, Share).Value
     SourceCst = Worksheets(NBG_SourceRegionDataWorksheetName).Cells(Row_S, "A").Value
     SourcePID = Worksheets(NBG_SourceRegionDataWorksheetName).Cells(Row_S, ProjectID).Value
     ' Take the data from NBG_Data_Region sheet to be Sourceared with each row of the NBG_Data_Source_Region sheet
    For Row_T = 2 To MAX_Row_T

    If Row_T >= MAX_Row_T Then
      Exit For
    End If

    'NBGMonth = Worksheets(NBG_RegionaDataWorksheetName).Cells(Row_T, SOP).Value
    'NBGMonth = DatePart("m", NBGMonth)
    NBGYear = Worksheets(NBG_RegionaDataWorksheetName).Cells(Row_T, SOP).Value
    NBGYear = DatePart("yyyy", NBGYear)
    NBGCarmaker = Worksheets(NBG_RegionaDataWorksheetName).Cells(Row_T, carmaker).Value
    NBGProject = Worksheets(NBG_RegionaDataWorksheetName).Cells(Row_T, Project).Value
    NBGFamily = Worksheets(NBG_RegionaDataWorksheetName).Cells(Row_T, Family).Value
    NBGStatus = Worksheets(NBG_RegionaDataWorksheetName).Cells(Row_T, Status).Value
    NBGShare = Worksheets(NBG_RegionaDataWorksheetName).Cells(Row_T, Share).Value
    NBGCst = Worksheets(NBG_RegionaDataWorksheetName).Cells(Row_T, "A").Value
    NBGPID = Worksheets(NBG_RegionaDataWorksheetName).Cells(Row_T, ProjectID).Value

 'Get the number of rows in Issue_SumofShares
'SoS = SumofShares
MAX_Row_inSoS = Worksheets(Issue_SumofSharesWorksheetName).UsedRange.RowS.Count
S = MAX_Row_inSoS
     Repetition = Application.WorksheetFunction.VLookup(Worksheets(NBG_RegionaDataWorksheetName).Cells(Row_T, ProjectID).Value, Worksheets(Issue_SumofSharesWorksheetName).Range("A2:AS"), 1)


 ' StatusBar Show
 Application.StatusBar = "VerifySumofShares. Progress: " & Row_S & " of " & MAX_Row_S
       'Check if any row with a SOP date in the previous or current years and if it is a D-IN or OPP is found and add it to the IssueSOP_Date sheet
       ' NAF 20161208
       'Test with Source of YEAR and MONTH
       ' If (NBGMonth = SourceMonth And NBGYear = SourceYear And SourceCarmaker = NBGCarmaker And SourceProject = NBGProject And SourceFamily = NBGFamily And SourceShare + NBGShare <> 1 And NBGCst <> SourceCst) Then
       ' With Year only
        If (NBGYear = SourceYear And SourceCarmaker = NBGCarmaker And SourceProject = NBGProject And SourceFamily = NBGFamily And SourceShare + NBGShare <> 1 And NBGCst <> SourceCst) Then
            If IsError(Repetition) = False Then
            GoTo Line1
              Else: GoTo Line2
              End If

       Line2:
              'Customization of the Issue_SumofShares sheet to show the NBG Data Row , Cst, SOP , Product, Responsible,Family , Carmaker , Share , Status and the GeoRegion of the data which the condition applies to

            Worksheets(Issue_SumofSharesWorksheetName).Cells(3 + Issue_SumofSharesCnt, "A").Value = Worksheets(NBG_RegionaDataWorksheetName).Cells(Row_T, ProjectID).Value
            Worksheets(Issue_SumofSharesWorksheetName).Cells(3 + Issue_SumofSharesCnt, "B").Value = Worksheets(NBG_RegionaDataWorksheetName).Cells(Row_T, Customer).Value
            Worksheets(Issue_SumofSharesWorksheetName).Cells(3 + Issue_SumofSharesCnt, "C").Value = GetMonthAndQuarter(Worksheets(NBG_RegionaDataWorksheetName).Cells(Row_T, SOP).Value)
            Worksheets(Issue_SumofSharesWorksheetName).Cells(3 + Issue_SumofSharesCnt, "D").Value = Worksheets(NBG_RegionaDataWorksheetName).Cells(Row_T, Product).Value
            Worksheets(Issue_SumofSharesWorksheetName).Cells(3 + Issue_SumofSharesCnt, "E").Value = Worksheets(NBG_RegionaDataWorksheetName).Cells(Row_T, Family).Value
            Worksheets(Issue_SumofSharesWorksheetName).Cells(3 + Issue_SumofSharesCnt, "F").Value = Worksheets(NBG_RegionaDataWorksheetName).Cells(Row_T, Project).Value
            Worksheets(Issue_SumofSharesWorksheetName).Cells(3 + Issue_SumofSharesCnt, "G").Value = Worksheets(NBG_RegionaDataWorksheetName).Cells(Row_T, carmaker).Value
            Worksheets(Issue_SumofSharesWorksheetName).Cells(3 + Issue_SumofSharesCnt, "H").Value = Worksheets(NBG_RegionaDataWorksheetName).Cells(Row_T, Share).Value
            Worksheets(Issue_SumofSharesWorksheetName).Cells(3 + Issue_SumofSharesCnt, "I").Value = Worksheets(NBG_RegionaDataWorksheetName).Cells(Row_T, Responsible).Value
            Worksheets(Issue_SumofSharesWorksheetName).Cells(3 + Issue_SumofSharesCnt, "K").Value = Worksheets(NBG_RegionaDataWorksheetName).Cells(Row_T, Status).Value
            Worksheets(Issue_SumofSharesWorksheetName).Cells(3 + Issue_SumofSharesCnt, "L").Value = Worksheets(NBG_RegionaDataWorksheetName).Cells(Row_T, "BO").Value
            ' Region As String
            Region = ""
            'Add any other GeoRegion which is also responsible in the recorded data

            If Worksheets(NBG_DataWorksheetName).Cells(Row_T, "BC") Then
            Region = Region + "@EMEA"
            End If
            If Worksheets(NBG_DataWorksheetName).Cells(Row_T, "BD") Then
            Region = Region + "@AMERICAS"
            End If
            If Worksheets(NBG_DataWorksheetName).Cells(Row_T, "BE") Then
            Region = Region + "@GCSA"
            End If
            If Worksheets(NBG_DataWorksheetName).Cells(Row_T, "BF") Then
            Region = Region + "@JAPAN&KOREA"
            End If
            Worksheets(Issue_SumofSharesWorksheetName).Cells(3 + Issue_SumofSharesCnt, "J").Value = Region


            'Count the number of the cases recorded
            Issue_SumofSharesCnt = Issue_SumofSharesCnt + 1
            'If there is no items , the Message to show
        ElseIf (Worksheets(NBG_SourceRegionDataWorksheetName).Cells(Row_S, SOP).Value = "There are no items to show in this view.") Then
     End If
  Line1:
     Next Row_T
     Next Row_S

Answer 1

Repetition = Application.WorksheetFunction.VLookup( _
      Worksheets(NBG_RegionaDataWorksheetName).Cells(Row_T, ProjectID).Value, _
      Worksheets(Issue_SumofSharesWorksheetName).Range("A2:AS"), 1)

如果找不到值的值，这将丢弃运行时错误。使用这种方法更安全：

Repetition = Application.VLookup(...)
If Not IsError() Then
   'was found
Else
   'not found
End If

省略WorksheetFunction意味着该函数将返回一个可以测试的错误值。另外，除非您真的想要默认行为，否则您应该始终将最后一个参数False包含在vlookup中。

作为附带说明，如果您使用变量参考您的床单，而是每次重复完整的路径：

Dim wsISOS As Worksheet
Set wsISOS = Worksheets(Issue_SumofSharesWorksheetName)