"Subscript out of range" when used in Loop

[Halkyon]

Hi folks,

I did look at the other threads about this but couldn't find an answer within.

My situation is as follows. I have a sheet that generates a list of file names to be imported into different sheets, I'm writing code to loop through the sheets and import them all into each (each list of files is in a different column named after the sheet the files will be imported into). I think I've got my head around most of it, but when my macro gets to the import stage it says "subscript out of range" on the array that holds all the file names (DataFiles).

This part of my code that highlights when I hit debug.

For DFCounter = 0 To NoOfProducts

[COLOR=SeaGreen]'File Import/Data Connection[/COLOR]
With ActiveSheet.QueryTables.Add(Connection:= _
   "TEXT;Macintosh HD:Users:user:path:" & DataFiles(DFCounter) & ".csv", _
   Destination:=ImportTarget)
  .LotsOfProperties = Variables
End With

This is how the array is populated:

With Sheets("Inputs_DataIDs").Range(DataColumns(SetCounter) & 2, DataColumns(SetCounter) & NoOfProducts + 1)
    ReDim DataFiles(0 To NoOfProducts)
    DataFiles = Range(DataColumns(SetCounter) & 2, DataColumns(SetCounter) & NoOfProducts + 1)
End With

My code worked before I used the With... function to populate the array, I was doing that manually before.

 

[Norie]

Check the dimensions of DataFiles.

 

[Halkyon]

Thank you for your reply Norie.

The dimensions are defined by NoOfProducts, which I can see in the debug window is 64 at the moment (correct). So...

ReDim DataFiles(0 To NoOfProducts)

...should fit my range of 64 rows in a single column, no?

How would I check what is being passed into my array?

 

[Norie]

If you want to check the contents, and dimensions of the array add a watch on it.

To do that just double click DataFiles, right click and select Add Watch...

To check the array set a breakpoint (F9) at End With and run the code.

 

[mikerickson]

Try adding parenthesis
..., DataColumns(SetCounter) & (NoOfProducts + 1))

 

[Halkyon]

Try adding parenthesis
..., DataColumns(SetCounter) & (NoOfProducts + 1))

Unfortunately doesn't work.

I added a watch to the array (fantastic tip). Its dimensions are correct but entirely empty so I imagine the problem lies with my code to populate it from the range. Perhaps it has something to do with my somewhat primitive method of passing the columns as text strings (e.g. "I"). Although I did try it using the...

.Range(Cells(x,x), Cells(x,x))

... method but was presented with a different error, of course I may have implemented it incorrectly.

Edit: It was "Method '_Default of object 'Range' failed", debugger highlights first line.

With Sheets("Inputs_DataIDs").Range(Cells(2, DataColumns(SetCounter)), Cells((NoOfProducts + 1), DataColumns(SetCounter)))
   ReDim DataFiles(0 To NoOfProducts)
   DataFiles = Range(Cells(2, DataColumns(SetCounter)), Cells((NoOfProducts + 1), DataColumns(SetCounter)))
End With

 

[Norie]

Can you check again?

When I run your code with the NoProducts =64, SetColumn as 1 and DataColumns as ("A", "B") I end up with an array with the dimensions 1 to 64, 1 to 1.

To reference a value in that array I need to use both dimensions.

Msgbox DataFiles(34, 1)  ' return 34 value in array

 

[Norie]

PS The array could be empty because there's no . before Range, so it refers to the active sheet not the sheet in the With.

 

[Halkyon]

Right, I've checked it this morning with ".Range" instead of "Range" and it looks like the array is getting populated, looks there is something funny going on with the size. Further investigation required...

 

[Halkyon]

I've got the array to populate properly now, it looks fine in the watcher but I'm still getting the same error.

Could there be something wrong with the counters?