Updated the vba code and still it gives me a subsc

问题:

This question already has an answer here:

• Subscript out of range error in this Excel VBA script 3 answers

This code still gives me an out of subscript error

Sub importData2()

  ChDir "C:\Users\Desktop\Java"
  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")

I avoided using .active and .select. .select.

回答1:

Subscript out of Range would raise on that line if the required file is not already 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.

Set wb2 = Application.Workbooks.Open(filenum(lngposition) & ".csv").

Updated your code

Sub importData2()

  ChDir "C:\Users\Desktop\Java"
  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

回答2:

You should definitely have Set on the line when you assign worksheets:

Set sh1 = Worksheets(filenum(lngPosition))