我对 VBA 世界非常陌生。
我想从不同工作簿中的 2 个工作表中选择 2 个范围。
但是我在我的脚本中收到了这条消息。请帮忙
Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim wb1 As Workbook, wb2 As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet
Dim rng1 As Range, rng2 As Range
Set wb1 = Workbooks("TEMPLATE.xls")
Set ws1 = Sheets("macro")
Set rng1 = Range(Range("C3"), Range("C3").End(xlDown)).Select
Set wb2 = Workbooks("booking.xlsx")
Set ws2 = Sheets("ABC")
Set rng2 = Range(Range("L3"), Range("L3").End(xlDown)).Select
End Sub
一直在尝试和测试。
我试图通过创建您的代码中提到的书籍和工作表来复制您的错误。向宏表中添加了一个 CommandButton1,并将您的代码插入到其 Click 事件处理程序中。
尝试单步执行代码确实选择了宏表上的范围,但抛出了错误 424(需要对象)并且代码执行终止而没有突出显示 IDE 中的任何内容。
改变:
Set rng1 = Range(Range("C3"), Range("C3").End(xlDown)).Select
至
Set rng1 = Range(Range("C3"), Range("C3").End(xlDown))
rng1.Select
摆脱了错误,但下一步在 上给出了错误 9(下标超出范围)Set ws2 = Sheets("ABC")
。这是因为 TEMPLATE.xls 仍然是活动工作簿。
更改Set ws2 = Sheets("ABC")
为Set ws2 = wb2.Sheets("ABC")
清除此错误,但由于.Select
上Set Rng2
线再次出现错误 424 。此外,选择了宏表上 L 列中的单元格。
改变:
Set rng2 = Range(Range("L3"), Range("L3").End(xlDown)).Select
至:
Set rng2 = Range(Range("L3"), Range("L3").End(xlDown))
rng2.Select
摆脱了那个错误,但宏表中的单元格仍然被选中。
为了解决这个问题,我改变了:
Set rng2 = Range(Range("L3"), Range("L3").End(xlDown))
rng2.Select
至:
With ws2
Set rng2 = Range(.Range("L3"), .Range("L3").End(xlDown))
End With
rng2.Select
我以为已经解决了它,但后来得到了一个“错误 1004(Range 类的选择方法失败)”,我只能通过激活 bookings.xlsx 来克服它!
您需要明确声明您的表格和范围:
Sub CommandButton1_Click()
Dim wb1 As Workbook, wb2 As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet
Dim rng1 As Range, rng2 As Range
Application.ScreenUpdating = False
Set wb1 = Workbooks("TEMPLATE.xls")
Set ws1 = wb1.Sheets("macro")
With ws1
Set rng1 = .Range(.Range("C3"), .Range("C3").End(xlDown))
rng1.Select
End With
Set wb2 = Workbooks("booking.xlsx")
wb2.Activate
Set ws2 = wb2.Sheets("ABC")
With ws2
Set rng2 = .Range(.Range("L3"), .Range("L3").End(xlDown))
End With
rng2.Select
wb1.Activate
Application.ScreenUpdating = True
End Sub
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句