Named array of variable size

[pingme89]

I want to name an array to use in a formula with Index and Match.

Specifically speaking, I have 2 Hidden sheets. Every time the spreadsheet opens, the user is prompted to select 2 files. The contents of each file will be copied onto each of the Hidden Sheets. The problem is that I don't know how to declare the array with the array size changing depending on the user selected file.

I tried this but I get an error saying that the values must be constants.

Dim HRange(1 To HRow, 1 To HCol) As String
Dim DRange(1 To DRow, 1 To DCol) As String
Dim HUnitRange(1 To HRow, HUnitCol To HUnitCol) As String
Dim HVINRange(1 To HRow, HVINCol To HVINCol) As String
Dim DUnitRange(1 To DRow, DUnitCol To DUnitCol) As String
Dim DVINRange(1 To DRow, DVINCol To DVINCol) As String

HRow, HCol, DRow, DCol are integers and the values are obtained by counting the number of rows of each user selected file.

On sheet1 I want to use as Index and Match combination referring to the User Selected files. Can someone explain to me how this can be done?

Thanks

 

[Tetra201]

Try:

...
Dim HRange() As String
ReDim HRange(1 To HRow, 1 To HCol)
...

 

[pingme89]

Try:

...
Dim HRange() As String
ReDim HRange(1 To HRow, 1 To HCol)
...

Thanks. The array declaration problem went away, but now I have a problem setting the array.

HLastAddress = Worksheets("HData").Cells(HRow, HCol).AddressDLastAddress = Worksheets("DData").Cells(DRow, DCol).Address


Set HRange = ActiveWorkbook.Worksheets("HData").Range("A1:" & HLastAddress)
Set DRange = ActiveWorkbook.Worksheets("DData").Range("A1:" & DLastAddress)

I can't see what is wrong.

 

[Tetra201]

Try this (no need to ReDim ... As ...):

...
Dim HRange()
HRange = ActiveWorkbook.Worksheets("HData").Range("A1:" & HLastAddress)
...

 

[pingme89]

I now get a Run-Time Error '13':
Type Mismatch