更新了 vba 代码,但它仍然给我一个下标超出范围的错误 [英] Updated the vba code and still it gives me a subscript out of range error
本文介绍了更新了 vba 代码,但它仍然给我一个下标超出范围的错误的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
这段代码仍然给我一个下标错误
This code still gives me an out of subscript error
Sub importData2()
ChDir "C:UsersDesktopJava"
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
Dim rng As Range
Set rng = Range(Selection, ActiveCell.SpecialCells(xlLastCell))
Dim wb As Workbook
Set wb = Application.Workbooks("30_graphs_w_Macro.xlsm")
Dim sh2 As Worksheet
Dim rng2 As Range
Set rng2 = Range("A69")
Dim wb2 As Workbook
For lngposition = LBound(filenum) To UBound(filenum)
Set wb2 = Application.Workbooks.Open(filenum(lngposition) & ".csv")
wb2.Worksheets(filenum(lngposition)).rng.Copy wb.Worksheets(filenum(lngposition)).rng2.Paste
Next lngposition
my_handler:
MsgBox "All done."
End Sub
这仍然给我一个下标错误:
This still gives me an out of subscript error on the line:
Set wb2 = Application.Workbooks(filenum(lngposition) & ".csv")
我避免使用 .active 和 .select..选择.
I avoided using .active and .select. .select.
推荐答案
Subscript out of Range
如果所需文件尚未打开,则会在该行引发.
Subscript out of Range
would raise on that line if the required file is not already open.
由于您似乎不太可能已经打开了 11 个文件,因此您可能需要使用 Open
方法在循环中打开必要的工作簿.
Since it seems unlikely that you would already have 11 files open, you probably need to use the Open
method to open the necessary workbook inside your loop.
设置 wb2 = Application.Workbooks.Open(filenum(lngposition) & ".csv")
.
更新了您的代码
Sub importData2()
ChDir "C:UsersDesktopJava"
Dim filenum(0 To 10) As String
Dim wb As Workbook
Dim sh1 As Worksheet
Dim rng As Range
Dim wb2 As Workbook
Dim sh2 As Worksheet
Dim rng2 As Range
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"
'## What workbook is this referring to?? This might cause problems later...
Set rng = Range(Selection, ActiveCell.SpecialCells(xlLastCell))
Set rng2 = Range("A69")
Set wb = Application.Workbooks("30_graphs_w_Macro.xlsm")
For lngposition = LBound(filenum) To UBound(filenum)
Set wb2 = Application.Workbooks.Open(filenum(lngposition) & ".csv")
Set sh1 = wb.Worksheets(filenum(lngposition))
Set sh2 = wb2.Worksheets(1) 'A CSV file only has 1 worksheet.
sh2.rng.Copy Destination:=sh1.Range(rng2.Address)
Next lngposition
my_handler:
MsgBox "All done."
End Sub
这篇关于更新了 vba 代码,但它仍然给我一个下标超出范围的错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文