Excel For循环和通用vba指南

Question

我似乎不明白为什么这个按钮会给出运行时错误"424"。我不明白是不是for循环导致了它的失败。我试着用.select替换.activate，但什么也没发生。

Private Sub updateX_Click()
Dim emptyRow As Long
Dim emptyRowX As Long
Dim counter
Sheets("Master Inventory").Activate
Sheets("X").Activate
'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Worksheets("Master Inventory").Range("A:A")) + 1
emptyRowX = WorksheetFunction.CountA(Worksheets("X").Range("A:A")) + 1
For counter = 1 To emptyRow
If MI.Cells(counter, 1).Value = "X" Then
X.Cells(emptyRowX, 1).Value = MI.Cells(counter, 2).Value
X.Cells(emptyRowX, 2).Value = MI.Cells(counter, 3).Value
X.Cells(emptyRowX, 3).Value = MI.Cells(counter, 4).Value
X.Cells(emptyRowX, 4).Value = MI.Cells(counter, 5).Value
X.Cells(emptyRowX, 5).Value = MI.Cells(counter, 6).Value
End If
Next counter
End Sub

Answer 1

以下更正如何：

Dim counter As Long <-- this you already have (but you forgot the type definition)
Dim MI As Worksheet, X As Worksheet
Set MI = Worksheets("Master Inventory")
Set X = Worksheets("X")
Sheets("Master Inventory").Activate <-- this you already have