Array Help Please

[business_analyst]

Hello All,

I have multiple tables (all the same size) on a worksheet that are filled with rates. I would like to be able to populate a different array for each of these tables. Since there are several tables on the sheet, I am trying to create a Sub routine in a module so that I can simply call that rather then writing the same code over and over with small variations. Here is what I have so far (keep in mind the body of the code has been run and does work to create a specific array, I am trying to modify it to be called several times):

Sub Sheet_Rate_Array(ByVal row_offset As Integer, ByVal col_offset As Integer, ByRef Rate_Array() As Variant)

    Dim row As Integer
    Dim col As Integer
    
    For col = 1 To 10
        For row = 1 To 71
            Rate_Array(row, col) = Sheets("Rate_Charts").Cells(row + row_offset, col + col_offset).value
        Next row
    Next col
    
End Sub

Basically this code allows me to call Sheet_Rate_Array, while including the row and column numbers on the worksheet where the specific chart starts. The issue is with the Array declaration. As you can see in the Sub, the array needs to have two dimensions, how can I declare this? Also, does the logic make sense to declare the Array in the first place? My thought was to do this in order to be able to create a different array everytime I call this procedure (ex. Rate1_Array, Rate2_Array, etc.).

 

[Norie]

Why do you need these arrays in the first place?

If you do want to use arrays then the first thing you'll need to do is use ReDim, how I don't know.

Can't you just use ranges?

By the way you can create an array from a range with one line of code.

Rate_Array = Worksheets("Rate_Charts").Range("A1:A10").Resize(10,5)