VBA:下标超出范围 [英] VBA: Subscript out of range
本文介绍了VBA:下标超出范围的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
类似的问题已经被问到,但我想我有一个不同的问题:
similar questions have been asked but I think I have a different problem:
Workbooks.Open Filename:=filepath & "PLT.xlsx"
Worksheets("Sheet1").Range(Worksheets("Sheet1").Range("A1:B1"), Worksheets("Sheet1").Range("A1:B1").End(xlDown)).Copy
Windows("XXX.xslm").Activate
w1.Range("A4").PasteSpecial Paste:=xlPasteValues
第二行是问题。其实它不会复制我想要的单元格。当我打开该工作簿时,会选择整个工作表。
The second line is the problem. In fact, it does not copy the cells I want. When I open that workbook, the whole worksheet is selected.
我不明白为什么我会收到该错误。
I do not understand why I get that error.
推荐答案
Yikes。如果要将值复制到它,那么简单的方法是:
Yikes. If you want to copy values to it the easy way:
Global fso As New FileSystemObject
Public Sub CopyValuesTest()
' Get references to the files
Dim wb1 As Workbook, wb2 As Workbook
Set wb1 = Workbooks.Open(fso.BuildPath(filepath, "PLT.xlsx"))
Set wb2 = Workbooks("XXX")
' Get references to the sheets
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = wb1.Sheets("Sheet1")
Set ws2 = wb2.Sheets("Sheet1")
' Count non-empty rows under A1. Use 2 columns
Dim N As Integer, M As Integer
N = CountRows(ws1.Range("A1")): M = 2
' This copies the values
ws2.Range("A4").Resize(N, M).Value = ws1.Range("A1").Resize(N, M).Value
End Sub
Public Function CountRows(ByRef r As Range) As Long
If IsEmpty(r) Then
CountRows = 0
ElseIf IsEmpty(r.Offset(1, 0)) Then
CountRows = 1
Else
CountRows = r.Worksheet.Range(r, r.End(xlDown)).Rows.Count
End If
End Function
并确保您的文件路径
已定义。另请使用 FileSystemObject
请参阅 https://stackoverflow.com/a/5798392/ 380384
And make sure your filepath
is defined. Also to use FileSystemObject
see https://stackoverflow.com/a/5798392/380384
这篇关于VBA:下标超出范围的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文