1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel question

Discussion in 'Business Applications' started by Born2Race, Jan 24, 2005.

Thread Status:
Not open for further replies.
Advertisement
  1. Born2Race

    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!
     
  2. Gazornenplat

    Gazornenplat

    Joined:
    Dec 30, 2004
    Messages:
    106
    Dim a()
    a = Range("A1", "C3")


    Ian
     
  3. Born2Race

    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")
     
  4. Gazornenplat

    Gazornenplat

    Joined:
    Dec 30, 2004
    Messages:
    106
    Has anything been assigned to array1 before? That line works for me.

    Ian
     

    Attached Files:

  5. Born2Race

    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?
     
  6. Gazornenplat

    Gazornenplat

    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
     
  7. Born2Race

    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().
     

    Attached Files:

  8. Gazornenplat

    Gazornenplat

    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
     
  9. Born2Race

    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!
     
  10. Gazornenplat

    Gazornenplat

    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
     
  11. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/322964

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice