Updated the vba code and still it gives me a subscript out of range error

Question

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.

One tip is to remove the On Error GoTo statement; this will show which line is causing the error and better direct what isn't working. — Richard Morgan, Nov 06 '13 at 15:12
Agree with Richard.... that's not an error handler but a false completion message. Also I would remove all of your .Select and Selection. and .Activate references. When you create a macro in Excel it defaults to this language but that's really bad coding. For instance your sh1.Activate, Range("A69").Select and ActiveSheet.Paste lines can be replaced to sh1.Range("A69").Paste. Once you have it cleaned up and remove your On err line you'll be able to properly debug. — , Nov 06 '13 at 15:38
Ok yeah.. that did help, and thanks for the tips. I made the changes in the code. So now I get the message the file 052.csv could not be found. This excel file (with the script) is in the same location as the file 052.csv itself. — user2883071, Nov 06 '13 at 15:46
It gives me a runtime error 1004 on this line: Workbooks.Add(filenum(lngPosition) & ".csv").Activate saying it can find the file — user2883071, Nov 06 '13 at 15:49
This is not necessarily your current directory. Use `CurDir` function to find where you are and `ChDir` to change it. — martin, Nov 06 '13 at 15:50
Yes, that took it a litle bit further.. now i get a subscript out of range on this line Set sh1 = Worksheets(filenum(lngPosition)) — user2883071, Nov 06 '13 at 15:54
You don't need to ask a new question. Simply edit the old question as they both are the same literally. What is the value of `lngposition` when you get the error? — Siddharth Rout, Nov 06 '13 at 17:20
Is the file open in excel already? or are you trying to open it in the code? — Sam, Nov 06 '13 at 17:21
Its not subscript out of range for filenum array it is subscript out of range for Workbooks. — Motomotes, Nov 06 '13 at 17:22
So should I go back to the old question and reedit the post? I thought this would affect other viewers as then they will not be able to see my old mistakes — user2883071, Nov 06 '13 at 17:25
If anyone wants, they can always see the revision history. Until and unless it is a completely different question, you do not need to ask separate questions :) — Siddharth Rout, Nov 06 '13 at 17:26
Alright.. anyway i can take this question down? i can update the old one then — user2883071, Nov 06 '13 at 17:27

David Zemens · Accepted Answer · 2013-11-06T18:16:37.637

3

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

edited Nov 06 '13 at 18:16

answered Nov 06 '13 at 17:21

David Zemens

53,033
11
81
130

2

This is a duplicate question :) http://stackoverflow.com/questions/19817399/subscript-out-of-range-error-in-this-excel-vba-script – Siddharth Rout Nov 06 '13 at 17:22
2

that's the 3rd time I've seen that bit of code... http://stackoverflow.com/questions/19799320/how-do-i-resolve-the-file-mismatch-in-this-vba-script – Sam Nov 06 '13 at 17:24
This opens up the file, and then it gives me the out of subscript error on the next line: wb2.Worksheets(filenum(lngposition)).rng.Copy wb.Worksheets(filenum(lngposition)).rng2.Paste – user2883071 Nov 06 '13 at 17:25
Do **both** workbooks (`wb` and `wb2`) contain a worksheet which corresponds excactly to the name defined by `filenum(lngposition)`? – David Zemens Nov 06 '13 at 17:27
yes.. both workbooks have their sheets named 052 and so on – user2883071 Nov 06 '13 at 17:28
`052` is **NOT** in the `filenum` array. What is the value of `filenum(lngposition)`? And please confirm that string matches a worksheet name in both files. – David Zemens Nov 06 '13 at 17:29
Oops.. yes I edited the post again.. and yes they do match.. wb1 and wb2 have a sheet with 052 as the name, also wb2 has the name 052.csv – user2883071 Nov 06 '13 at 17:31
Try: `wb.Worksheets(filenum(lngposition)).rng2.Value = wb2.Sheets(1).rng.Value` – David Zemens Nov 06 '13 at 17:38
I get the error object does not support this property or method – user2883071 Nov 06 '13 at 17:40
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/40655/discussion-between-user2883071-and-david-zemens) – user2883071 Nov 06 '13 at 17:48
I rewrote your code. Try the answer above and let me know if you still get any errors. – David Zemens Nov 06 '13 at 17:49
It gives a method or data member not found error at sh2.rng.Copy Destination:=sh.rng2 I changed sh to sh1 and then it gives a missing data member at .rng2 – user2883071 Nov 06 '13 at 18:02
Try `sh1.Range(rng2.Address)` or simply `sh1.Range("A69")`. – David Zemens Nov 06 '13 at 18:06
So i did that.. then it gave me the message sh2.rng. is not a data member.. so i replaced that with sh2.Range(Selection, ActiveCell.SpecialCells(xlLastCell)). now it gives me the subscript out of range error at Set sh1 = wb.Worksheets(filenum(lngposition)) – user2883071 Nov 06 '13 at 18:08
Try `Dim filenum(0 to 10) As String` per my revision above. A value like `052` when stored as `Long` data type will convert to `52`. If the worksheet name is `052` then this error is expected. Although I would still expect an error in the `Set sh1 ...` statement. – David Zemens Nov 06 '13 at 18:18
Yes String works.. thanks! – user2883071 Nov 06 '13 at 18:20
Do consider "Accepting" this answer... – David Zemens Nov 06 '13 at 18:32
@SamWard This is the fourth time I've seen this code: http://stackoverflow.com/questions/19815472 – Richard Morgan Nov 06 '13 at 19:21
@RichardMorgan yikes. Well I think there was some confusion on OP's part. Several (many) issues/problems in the code, and s/he was under the impression that each discovered error requires a new question... – David Zemens Nov 06 '13 at 19:26
Yeah, I thought as I made changes I should open up a new post, so that other people could see my old mistakes – user2883071 Nov 06 '13 at 21:38
any chance i can take these questions down? – user2883071 Nov 06 '13 at 21:39
nah just leave them up at this point, especially if you have accepted answers on any of them. If some of them maybe do not have answers yet, then you might remove those. – David Zemens Nov 06 '13 at 23:26

score 1 · Answer 2 · answered Nov 06 '13 at 15:47

1

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

Set sh1 = Worksheets(filenum(lngPosition))

answered Nov 06 '13 at 15:47

martin

2,520
22
29

Thanks.. made the change – user2883071 Nov 06 '13 at 15:48

Updated the vba code and still it gives me a subscript out of range error

2 Answers2

Linked

Related