在此excel vba脚本中下标超出范围错误 [英] Subscript out of range error in this excel vba script
问题描述
我想将数据从csv文件复制到excel工作表中。有11个csv文件。
到目前为止,我有这个(它是从上一篇文章的修改版本):
I would like to copy data from a csv file into an excel worksheet. There are 11 csv files. So far I have this (it is a modified version from a previous post):
Sub importData()
Dim filenum(0 To 10) As Long
filenum(0) = 052
filenum(1) = 060
filenum(2) = 064
filenum(3) = 068
filenum(4) = 070
filenum(5) = 072
filenum(6) = 074
filenum(7) = 076
filenum(8) = 178
filenum(9) = 180
filenum(10) = 182
Dim sh1 As Worksheet
On Error GoTo my_handler
For lngPosition = LBound(filenum) To UBound(filenum)
'Windows(filenum(lngPosition) & ".csv").Activate
Workbooks.Add(filenum(lngPosition) & ".csv").Activate
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Windows("30_graphs_w_Macro.xlsm").Activate
Set sh1 = Worksheets(filenum(lngPosition)).Activate
Range("A69").Paste
Range("A69").Select
Next lngPositionlngPositionlngPosition
my_handler:
MsgBox "All done."
Exit Sub
End Sub
此代码给出了一个下标错误
This code gives me a subscript out of range error on the line
Set sh1 = Worksheets(filenum(lngPosition)).Activate
请帮助我解决这个问题。
此外,如果有任何其他提示如何改进此代码,请让我知道
Please help me fix this. And also, if there are any other tips on how to improve this code please let me know
推荐答案
设置sh1 = Worksheets(filenum(lngPosition))。激活
Set sh1 = Worksheets(filenum(lngPosition)).Activate
超出范围错误错误,因为它找不到该工作表。
You are getting Subscript out of range error
error becuase it cannot find that Worksheet.
也请...请不要使用 .Select / .Activate / Selection / ActiveCell
您可能想要查看如何避免在Excel VBA宏中使用选择。
Also please... please... please do not use .Select/.Activate/Selection/ActiveCell
You might want to see How to Avoid using Select in Excel VBA Macros.
这篇关于在此excel vba脚本中下标超出范围错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!