Array not accepting data

[TotallyConfused]

Hello

Below is a sample of the data file I'm working with. I'm not sure what will be shown here as I haven't used xl2BB, so I hope I'm doing this right. It should run from column 'A1 - H4' with column 'H' being blank at the moment.

CompareTables.xlsm
	H
6
Sheet1

That didn't seem to show all the data so I'll try once again below here. I hope this works as I've had nothing but problems with xl2BB.

CompareTables.xlsm
	B
8
Sheet1

Sub ArrayProblem()
    Dim Ary3() As Variant
    Dim Ary3Rows As Long
    Dim NumCols As Long
    Dim NumRows As Long
    NumCols = 7                        ' Other arrays used later loads the first 7 columns of data.
    NumRows = Cells(Rows.Count, "A").End(xlUp).Row
        MsgBox NumRows                                            ' This will show number 4 as it should.
    ReDim Ary3(1 To NumRows, 1 To NumCols + 1)   ' Extra column will be used later.
    Ary3 = Range("A1:H1")         ' Loads all of data row 1 from 'A1 to H1'. 'H1' is blank. Used later
        MsgBox Ary3(1, 7)            ' Will show 'T7' as it should.
    Ary3(1, 1) = 5                       ' If I use this line then
         MsgBox Ary3(1, 1)           ' will show the number 5 as it should. Now comes the problem.
    ' If I try the following line, I get the error message of: Runtime error 9, subscript out of range.
    Ary3(2, 1) = 5       ' Why an error message when Ary3(1,1) = 5 worked fine?
End Sub

' With the variable of 'Ary3Rows' set to '2' I tried the following line but with the same error message.

Ary3(Ary3Rows, 1) = 5

' I'm guessing this was caused from whatever the problem is that's described within the Sub.
' I hope so anyway, because I will need to use a variable instead of a number.

' In case you're wondering, Ary1 and Ary2 will hold this data and some other data which will be
' compared. Depending on comparison, information may be put into Ary3. I don't think any of
' that code should have any effect on my problem here. Ary3 is used just as a holding area until
' processing is completed. Then the data will be moved back to the spreadsheet.

' Any help you may be able to offer sure will be appreciated as I've wracked my brain trying to
' solve this problem. THANK YOU in advance.

' TotallyConfused

 

[mohadin]

Hi

 ReDim Ary3(1 To NumRows, 1 To NumCols + 1)   
    Ary3 = Range("A1:H1")

In the first line you redimed the Ary3 to 2 dimensions array OK
The when second line is executed will redim Art3 to one dimension
A1:H1 >> only one row
so Ary3(2, 1) is nor exist any way
Hope I could clear It for you

 

[TotallyConfused]

Hello Mohadin

I can't begin to THANK YOU enough for your simple, but extremely helpful and detailed explanation as to why I kept receiving error messages. Over a period of several days, I'd spent an untold number of hours wracking my brain trying to solve this problem. I had NO idea that entering a single row of data (what amounts to a 1-dimension array) into a 2-dimension array would redim it. This will be one mistake I won't make again for a long while.

THANKS AGAIN!
TotallyConfused

 

[Fluff]

The when second line is executed will redim Art3 to one dimension

Actually this is not quite right. Ary3 will still be a two dimensional array namely Variant(1 to 1, 1 to 8)
A one dimensional array would be just Variant(1 to 8)

 

[mohadin]

@Fluff

Ary3(2, 1) = 5 ' Why an error message when Ary3(1,1) = 5 worked fine?

 

[Fluff]

@mohadin
Not sure what you are saying, or asking.