vba - Excel VBA 代码疑难解答

Question

此代码的要点是从“删除标志”选项卡中获取用户输入，用户在其中输入项目编号及其所属程序，按项目编号和程序过滤“主列表”选项卡，然后将标志的名称与该列相匹配并删除该标志。然而，偏移量不起作用。相反，它删除了 header 。当我单步执行它时，一切正常，直到我用“********”标记的行。

我对 VBA 相当陌生，并且是自学的，因此非常感谢任何和所有帮助。非常感谢您的宝贵时间。

编辑:删除了“错误时继续下一步”并修复了一些拼写错误。当前的问题是 rng 在过滤时没有 >1 行，并且肯定有两行(一行是标题，一行是返回的数据。)

Sub RemoveFlag()
Dim cel As Range
Dim rg As Range
Dim d As Double
Dim i As Integer
Dim m As Integer
Dim n As Integer
Dim rng As Range
Dim wsMaster As Worksheet
Dim wsFlag As Worksheet
Set wsMaster = Worksheets("Master List")
Set wsFlag = Worksheets("Remove Flags")
i = 6

'If there is no data. Do nothing.
If wsFlag.Range("C6") = "" Then
    wsFlag.Activate
Else
    Application.ScreenUpdating = False

'Add Leading zeroes
wsFlag.Activate
Set rg = Range("C6")
Set rg = Range(rg, rg.Worksheet.Cells(Rows.Count, rg.Column).End(xlUp))
rg.NumberFormat = "@"
For Each cel In rg.Cells
If IsNumeric(cel.Value) Then
    d = Val(cel.Value)
    cel.Value = Format(d, "000000000000000000") 'Eighteen digit number
End If
Next

'Clear all the filters on the Master List tab.
    wsMaster.Activate
If wsMaster.AutoFilterMode = True Then
    wsMaster.AutoFilterMode = False
End If

'Loop through all lines of data
    Do While wsFlag.Cells(i, 3).Value <> ""
'Filter by the SKU number
wsMaster.Range("A1").AutoFilter Field:=4, Criteria1:=wsFlag.Cells(i, 3).Value
'Filter by the Program
    wsMaster.Range("A1").AutoFilter Field:=2, Criteria1:=wsFlag.Cells(i, 2).Value
'If the filter is not empty find the column of the flag
Set rng = wsMaster.UsedRange.SpecialCells(xlCellTypeVisible)

If (rng.Rows.Count > 1) Then
    wsMaster.Range("A1:Z1").Find(wsFlag.Cells(i, 4), LookIn:=xlValues).Activate
    n = ActiveCell.Column
    Sheets("Master List").Range.Offset(1, 0).SpecialCells(xlCellTypeVisible).Select
    m = ActiveCell.Row
    Cells(m, n) = ""
    wsFlag.Activate
    wsFlag.Range(Cells(i, 2), Cells(i, 4)).ClearContents
Else
    wsFlag.Activate
    wsFlag.Range(Cells(i, 2), Cells(i, 4)).Copy
    wsFlag.Range("F4").End(xlDown).Offset(1, 0).Activate
    ActiveCell.PasteSpecial Paste:=xlPasteValues
    wsFlag.Range(Cells(i, 2), Cells(i, 4)).ClearContents
End If
    wsMaster.Activate
    wsMaster.AutoFilterMode = False
i = i + 1
Loop

'Make sure the entire Master List tab is not highlighted and pull the 'highlighted cell' to A1 in both tabs.
    wsMaster.Activate
    wsMaster.Range("A1").Activate

wsFlag.Activate
Range("A1").Activate

'Unfreeze the screen
Application.ScreenUpdating = True

End If
End Sub

Answer 1

正如 @Zerk 建议的那样，首先在代码顶部设置两个工作表变量:

Dim wsMaster As Worksheet
Dim wsRemoveFlags As Worksheet

Set wsMaster = Worksheets("Master List")
Set wsRemoveFlags = Worksheets("Remove Flags")

然后将 Worksheets("Master List") 的所有其他实例替换为 wsMaster，并将 Worksheets("Remove Flags") 替换为 wsRemoveFlags。