Excel question

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Born2Race

Thread Starter
Joined
Jan 12, 2005
Messages
8
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!
 

Born2Race

Thread Starter
Joined
Jan 12, 2005
Messages
8
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")
 

Born2Race

Thread Starter
Joined
Jan 12, 2005
Messages
8
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?
 
Joined
Dec 30, 2004
Messages
106
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
 

Born2Race

Thread Starter
Joined
Jan 12, 2005
Messages
8
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

Joined
Dec 30, 2004
Messages
106
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
 

Born2Race

Thread Starter
Joined
Jan 12, 2005
Messages
8
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!
 
Joined
Dec 30, 2004
Messages
106
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
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Top