Tech Support Guy banner
Status
Not open for further replies.
1 - 10 of 10 Posts

·
Registered
Joined
·
8 Posts
Discussion Starter · #1 ·
Hi, is there any way to read the contents of an excel into an array easily?

lets say i have:

A B C
1 23 45 66
2 1 2 3
3 0 4 5

If I declare a multidimensional array as MyArray(3,3), can I use a loop to put the values into the array? If so, how?

Also, if I don't wish to read in all the contents, is it possible, if I were given the value "3" at some point, so I can go back to that table and select the corresponding values 2 and 1 and put them into an array?

Thanks for any help!
 

·
Registered
Joined
·
8 Posts
Discussion Starter · #3 ·
Thanks for the tip, but I can't seem to get it to work. When I run it it tells me "can't assign to array".

I assigned like this: Array1 = Sheets("Sheet1").Range("A2", "E25")
 

·
Registered
Joined
·
8 Posts
Discussion Starter · #5 ·
Hmm, I'm not sure what's going on. I still get "Can't assign to array", and the array is newly declared, so I think there's nothing in it? Sorry I'm kind of new to this.

I basically have sheet full of numbers and I have the following code in a command button on a userform:

Dim Array1()

Array1 = Sheets("Sheet1").Range("A2", "D25")

Does that store only 2 values into the array or everything from A2 to D25?
 

·
Registered
Joined
·
106 Posts
It will assign all the cells between the two extremes. Can you run the example I attached before?

Do you have anything strange in the cells?

Can you post an example file to have a look at?

Ian
 

·
Registered
Joined
·
8 Posts
Discussion Starter · #7 ·
Ya I can get your file to work when I click that button. Here is my file, hehe the code is pretty bad cuz I'm beginner so hope you don't mind. The array part is just at the beginning of cmdTest_Click().
 

Attachments

·
Registered
Joined
·
106 Posts
OK. Your problem is that you are referencing Sheets("Sheet7") and you don't have a sheet called that. You have a Sheet7, but it is called "Sheet5", so:

Either use Sheet7.Range(...
Or Sheets("Sheet5").Range(...

Also use

Array1

not

Array1()


See if that works.

Oh, and an Exit Sub afer each MsgBox "Please enter all values"


Ian
 

·
Registered
Joined
·
8 Posts
Discussion Starter · #9 ·
Thanks it works now!! Why put an exit sub after each msgbox though?

And also, how come I can't find the Form_Load event, because I want to store all these values into arrays right away when the program starts.

Thanks a lot!
 

·
Registered
Joined
·
106 Posts
I only said the exit sub because I didn't enter any data and got a whole stream of messageboxes to get through. Maybe highlighting all the fields in error would be better.

Use the Form_Activate event to preload stuff. It appears to be pretty much the same thing.

Ian
 
1 - 10 of 10 Posts
Status
Not open for further replies.
Top