excel - Application.WorksheetFunction.Match 没有按预期工作

Question

我正在使用具有 11 个工作表的 EXCEL 文件，在此配置中:
VOL_CODE = 我要查找的代码。如果存在于工作表中，则应删除包含 VOL_CODE 的整行。
工作表:

“ NEW VOL DATA ” - 这是在整个过程中“保持”(“事件”或“选定”)的工作表。
此外，这里是 K1:K10 范围内的指示(“X”)，是否选择了目标工作表。

“目标 1”，“目标2 ”，“目标3 10456792045679210445644564” X1104344343434343434343434 X4 X104X104X104X1043号
“目标”工作表，如果出现 VOL_CODE 并且工作表指示为“已选择”(在“新 VOL 数据”工作表中)，则应删除它的行。

代码:

Private Sub CommandButton3_Click()
'--- Remove VOL_CODE line from all worksheets -----
   Dim ws1, ws2 As Worksheet
   Dim VOL_CODE As String
   Dim PLA, LIN As Integer
   Set ws1 = Worksheets("NEW VOL DATA")   '--- This is the Working Worksheet -----
   VOL_CODE = “RS_123456” ‘--- This is the code to search for -----

   For PLA = 1 To 10
         If UCase(Range("K" & PLA)) <> "X" Then GoTo JUMP_PLA:
         Set ws2 = Worksheets("DESTINATION" & Trim(Str(PLA)))
         Do While True
            On Error GoTo JUMP_PLA:
            LIN = Application.WorksheetFunction.Match(VOL_CODE, ws2.Range("B:B"), 0)
            ws2.Cells(LIN, 1).EntireRow.Delete
        Loop
JUMP_PLA:
   Next PLA

End Sub

问题是，当我执行代码时，它在 DESTINATION1 工作表中运行良好，包含或不包含 VOL_CODE(如果包含，它会循环删除 VOL_CODE 的行，直到不再有)，然后，当找不到更多 VOL_CODE 条目时，它转到“JUMP_PLA:”和“下一个 PLA”...从那里重新开始，现在转到下一个“ DESTINATIONx ”工作表(下一个选择)...并且出现错误(找到或不是有效条目)当 Application.WorksheetFunction.Match 命令执行时:
执行错误:1004
应用程序定义或对象定义错误
我知道这一定是一个愚蠢的错误，但由于我是新手，我无法想象它。它让我发疯......
谁能给我一盏灯？将不胜感激，我提前感谢您。

Answer 1

Application.WorksheetFunction.Match 删除行

程序 doDest 最好复制到标准模块(例如 Module1 )中，然后在您的按钮代码中调用:

Private Sub CommandButton3_Click()
    doDest
End Sub

我把你的代码留在里面了，所以你可以看到错误和一些选项。

剩下的代码只是一些用来玩的玩具，因为我从未见过 Match 用于删除行。

如果您想玩，请将完整代码复制到 新 工作簿中的标准模块中，并将工作表重命名为 NEW VOL DATA 。在它的 K1:K10 范围内，在几个单元格中输入一个 x 就可以了。

代码

Option Explicit

Sub doDest()
'--- Remove VOL_CODE line from all worksheets -----
   
   ' Speed up the code (you won't see what the macro is doing).
   Application.ScreenUpdating = False
   
   Dim ws1 As Worksheet, ws2 As Worksheet 'Dim ws1, ws2 As Worksheet
   Dim VOL_CODE As String
   Dim PLA As Long, LIN As Long ' Dim PLA, LIN As Integer
   Dim wb As Workbook: Set wb = ThisWorkbook ' The workbook with this code.
   Set ws1 = wb.Worksheets("NEW VOL DATA") 'Set ws1 = Worksheets("NEW VOL DATA")   '--- This is the Working Worksheet -----
   VOL_CODE = "RS_123456" '--- This is the code to search for -----

   For PLA = 1 To 10
       'If StrComp(ws1.Range("K" & PLA).Value, "X", vbTextCompare) <> 0 _
         Then GoTo JUMP_PLA
       If UCase(ws1.Range("K" & PLA).Value) <> "X" Then GoTo JUMP_PLA ' If UCase(Range("K" & PLA)) <> "X" Then GoTo JUMP_PLA:
       Set ws2 = wb.Worksheets("DESTINATION" & PLA) ' Set ws2 = Worksheets("DESTINATION" & Trim(Str(PLA)))
       Do ' Do While True
           ' On Error GoTo JUMP_PLA:
           ' LIN = Application.WorksheetFunction.Match(VOL_CODE, ws2.Range("B:B"), 0)
           ' ws2.Cells(LIN, 1).EntireRow.Delete
           On Error Resume Next ' Turn ON error trapping.
           ' "ws2.Columns("B")" is just an option, you can stick with
           ' "ws2.Range("B:B")".
           LIN = Application.WorksheetFunction _
                            .Match(VOL_CODE, ws2.Columns("B"), 0)
           If Err.Number <> 0 Then
               On Error GoTo 0  ' Turn OFF error trapping.
               'Debug.Print "Done with worksheet '" & ws2.Name & "'."
               Exit Do ' or: GoTo JUMP_PLA
           Else
               On Error GoTo 0  ' Turn OFF error trapping.
               ws2.Cells(LIN, 1).EntireRow.Delete
           End If
       Loop
JUMP_PLA:
   Next PLA

   Application.ScreenUpdating = True

   MsgBox "Deleted rows containing '" & VOL_CODE & "'.", _
          vbInformation, "Success"

End Sub

' Deletes all sheets named "DESTINATIONx", where x is from 1 to 10.
Sub deleteDest()
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim i As Long
    For i = 1 To 10
        Application.DisplayAlerts = False ' To prevent Excel from 'complaining'.
        On Error Resume Next              ' If a sheet does not exist.
        wb.Sheets("DESTINATION" & i).Delete
        On Error GoTo 0
        Application.DisplayAlerts = True
    Next i
End Sub

' Adds worksheets named "DESTINATIONx", where x is from 1 to 10.
' In each of those worksheets, adds "RS_123456" to up to 100 cells
' in 'random' rows from 1 to 1000 in column 'B'.
Sub createDest()
    
   ' Speed up the code (you won't see what the macro is doing).
    Application.ScreenUpdating = False
    
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim ws As Worksheet, i As Long, j As Long, CurrName As String
    For i = 1 To 10
        CurrName = "DESTINATION" & i
        On Error Resume Next  ' Turn ON error trapping.
        Set ws = wb.Worksheets(CurrName)
        If Err.Number <> 0 Then
            ' Sheet with current name does not exist.
            Set ws = wb.Worksheets _
                       .Add(After:=wb.Worksheets(wb.Worksheets.Count))
            ws.Name = CurrName
        'Else ' Sheet with current name exists.
        End If
        On Error GoTo 0       ' Turn OFF error trapping.
        ws.Columns("B").Clear ' Ensures new data if sheets already exist.
        For j = 1 To 100
            ws.Cells(Application.WorksheetFunction.RandBetween(1, 1000), "B") _
              .Value = "RS_123456"
        Next j
    Next i
    wb.Sheets(1).Select
    
    Application.ScreenUpdating = True

End Sub

' Counts the number of cells in column 'B' containing a value.
Sub countDest()
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim i As Long
    For i = 1 To 10
        On Error Resume Next
        Debug.Print "DESTINATION" & i, wb.Worksheets("DESTINATION" & i) _
                                         .Columns("B") _
                                         .SpecialCells(xlCellTypeConstants) _
                                         .Cells.Count
        If Err.Number <> 0 Then
            Debug.Print "DESTINATION" & i, "No cells found."
        End If
    Next i
End Sub

' Ultimate test
Sub testDest()
    deleteDest ' Deletes the sheets.
    createDest ' Creates the worksheets with random data.
    countDest  ' Counts the cells containing "RS_123456" (Debug.Print).
    doDest     ' Deletes the rows containing "RS_123456" in column 'B'.
    countDest  ' Counts the cells containing "RS_123456" (Debug.Print).
    MsgBox "Ultimate: deleted, created, counted, done and counted again."
End Sub

' Initialize
Sub initCreateAndCount()
    deleteDest ' Deletes the sheets.
    createDest ' Creates the worksheets with random data.
    countDest  ' Counts the cells containing "RS_123456" (Debug.Print).
    MsgBox "Initialized: Sheets deleted and created, and cells counted."
End Sub

' Shows how even when the 'dest' sheets exist, new values are generated.
Sub testCreateCount()
    createDest ' Creates the worksheets with random data.
    countDest  ' Counts the cells containing "RS_123456" (Debug.Print).
    MsgBox "Sheets created and cells counted."
End Sub