Opening a workbook using array values

[joannetung]

Hello
I'm trying to figure out a way to open the workbooks using the array values stored. The only bug I encounter is at "Workbooks.Open(StoreList(0))" where I encounter "run-time error 0 subscript out of range" but the address is correct so I don't know why it can't find it?

Sub uniquestores()
    '
    ' uniquestores Macro
    '
    Dim lastrow As Long
    Dim x, y, z, uniquestores As Integer
    Dim StoreList(), paths() As String
    Dim csv, strPath As String
    Dim wbs As Workbook
    
    
strPath = "C:\Users\j.tung\Downloads\Excel VBA and Macros\SWIMLANE TAGGING\"
csv = ".csv"
'to count how many rows have data using column F
Sheets("Sheet1").Select
ActiveSheet.Range("F1").Select
Range(Selection, Selection.End(xlDown)).Select
lastrow = Selection.Cells.Count

'add new sheet and get the list of unique stores and count how many there are
    Sheets.Add After:=ActiveSheet
    ActiveSheet.Name = "uniquestorecount"
    Range("A1").Formula2 = "=UNIQUE(Sheet1!F2:F" & lastrow & ")"
    uniquestores = Cells(Rows.Count, "A").End(xlUp).Row
    
'to check
    Range("D1").Value = uniquestores
    Range("E1").Value = lastrow
    Range("F1").Value = strPath
    
'to get only the store name. without the "pandamart ()"
For y = 0 To uniquestores - 1
    Range("B" & y + 1).FormulaR1C1 = "=MID(RC[-1],12,LEN(RC[-1])-12)"
Next y

'to store the store names in an array. also adds ".csv" to be able to get workbook name
For x = 0 To uniquestores - 1
    ReDim StoreList(x To uniquestores)
    StoreList(x) = strPath & Range("B" & x + 1) & csv
    Range("C" & x + 1).Value = StoreList(x)
Next x

'delete the sheet to get the store names.  a pop-up will appear to ask if you really want to delete it.
'application.display will be triggered not to pop-up and it will automatically say "okay" to delete
    Application.DisplayAlerts = False
    Sheets("uniquestorecount").Delete
    Application.DisplayAlerts = True

'For z = 0 To uniquestores - 1
'paths(x) = Application.GetOpenFilename(FileFilter:="Excel Files (*.XLS), *.XLS", Title:="Chapter " + CStr(x))
'Application.DisplayAlerts = False
'paths(0) = Application.GetOpenFilename(FileFilter:="Microsoft Excel Comma Separated Values File (*.CSV), *.CSV")
Workbooks.Open(StoreList(0))
'Application.DisplayAlerts = True
'Next z


End Sub

 

[Fluff]

You are resizing the array inside the loop, which is causing the problem, try

ReDim StoreList(0 To uniquestores)
For x = 0 To uniquestores - 1
    StoreList(x) = strPath & Range("B" & x + 1) & csv
    Range("C" & x + 1).Value = StoreList(x)
Next x

 

[joannetung]

You are resizing the array inside the loop, which is causing the problem, try

ReDim StoreList(0 To uniquestores)
For x = 0 To uniquestores - 1
    StoreList(x) = strPath & Range("B" & x + 1) & csv
    Range("C" & x + 1).Value = StoreList(x)
Next x

Thanks for this. But I still can't seem to open the workbook because there's an error with the subscript out of range here

Workbooks.Open (StoreListPath(0))

This code I'm just first testing if it can open the first address but it can't.

 

[joannetung]

Thanks for this. But I still can't seem to open the workbook because there's an error with the subscript out of range here

Workbooks.Open (StoreListPath(0))

This code I'm just first testing if it can open the first address but it can't.

sorry my bad, I was running the wrong thing. It works thank you so much!!

 

[Fluff]

You're welcome & thanks for the feedback.