excel - VBA For循环工作一次，然后在第二次尝试它会创建一个运行时错误

Question

我创建了一个用于订购零件的系统，该系统运行一个宏来搜索运算符(operator)订购的零件，方法是查找一个表明该项目已订购的隐藏单元格。宏第一次按预期工作，但是当编辑工作簿(比如添加了一个项目)并再次运行宏时，它会生成运行时错误“1004”:应用程序定义或对象定义错误(错误行已注释)，

Set wb = ThisWorkbook
Set ws1 = wb.Sheets("PURCHASE ORDER")
Set ws2 = wb.Sheets("PICKING SHEET")

'Search for Ordered Units and add to Collection(Coll 9,"1" = Add row)
    For i = 19 To 150
        If Cells(i, 9).Value = 1 Then
            list.Add i
        End If
    Next i

'Clear Template
ws2.Range("A8:J45").Clear

'Add Purchase Date
ws1.Range("C5").Copy
ws2.Range("C5").PasteSpecial Paste:=xlPasteValues

'Add Order data
ws1.Range("E3:E5").Copy
ws2.Range("E3:E5").PasteSpecial Paste:=xlPasteValues

'Read Collection and add to "Order" sheet

For Each v In list
    'Debug.Print v
    'Formating Cell Borders
    ws2.Range("B8:J8").Offset(t).Borders(xlEdgeBottom).LineStyle = xlContinuous
    ws2.Range("B8:J8").Offset(t).Borders(xlInsideHorizontal).LineStyle = xlContinuous
    ws2.Range("B8:J8").Offset(t).Borders(xlInsideVertical).LineStyle = xlContinuous
    ws2.Range("B8:J8").Offset(t).Borders(xlEdgeLeft).LineStyle = xlContinuous
    ws2.Range("B8:J8").Offset(t).Borders(xlEdgeRight).LineStyle = xlContinuous
    'Copy Function
    Debug.Print v
    'Bug Postion - Only happens after macro retry, row is defined
    Set data1 = ws1.Range(Cells(v, 3), Cells(v, 5))
    Set data2 = ws1.Range(Cells(v, 10), Cells(v, 10))
    data1.Copy
    ws2.Range("B8").Offset(t).PasteSpecial Paste:=xlPasteValues
    data2.Copy
    ws2.Range("E8").Offset(t).PasteSpecial Paste:=xlPasteValues
    'Formating Cell Alingment
    ws2.Range("B8:J8").Offset(t).HorizontalAlignment = xlCenter
    'Paste Offset, every loop adds 1 and moves line to next row
    t = t + 1
Next v

任何帮助表示赞赏!

Answer 1

在每个 Cells 之前您应该指定 ws1或 ws2 .
例如:ws1.Cells
奖金。

ws1.Range("C5").Copy
ws2.Range("C5").PasteSpecial Paste:=xlPasteValues

相当于:

ws2.Range("C5") = ws1.Range("C5")

此外，这个较短的版本在性能方面更好。