Array not accepting data

TotallyConfused

Board Regular
Joined
May 4, 2017
Messages
247
Office Version
  1. 365
Platform
  1. Windows
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



VBA Code:
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.
VBA Code:
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
 
Last edited:

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Hi
VBA Code:
 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
 
Upvote 0
Solution
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
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top