Assigning array values with a loop

[Mr. Rogers]

So I'm working on a project to keep track of all the supplies I need to keep on hand in Mr. Rogers Neighborhood to keep all of my animals alive. I have hundreds of different sheets and I'm trying to just be able to input all of my values on 1 sheet and send them to their respective sheets, with the sheet name in column A and the destination range which I can update depending on what row I want to send the data to. (All of the data will be pasted in the same row and column of each respective worksheet.) This is the code I'm using that works:

Please Login or Register  to view this content.

It wouldn't be too difficult to write the code for hundreds of sheets by just concatenating a couple of different columns in Excel but I'm trying to learn VBA so I've been attempting to figure out a loop to assign values to the array:

Please Login or Register  to view this content.

and then a loop for the copy, paste function:

Please Login or Register  to view this content.

What I picture this code doing during the first i loop is taking the value in "A2" which is "cat" and make that value = dept(2)
second loop takes the value of "B3" which is "dog" and making that value = dept(3)
and once I have all of those values assigned it moves on to the j loop which I'm thinking (for the first iteration) copies the values in the range B2:E2 in the supplies sheet and then pastes it in the worksheet animal(2) which has been assigned the value of "cat" in the previous loop into the range that I've specified in beg and fin.

Alas, Mr. Rogers keeps running into range issues and now all of his wonderful critters are at risk of slowly and painfully withering away unless you wonderful people can help him figure out how to make his code work. Thanks in advance for all of your help!

[Norie]

Any chance you could upload a sample workbook?

Click on GO ADVANCED, scroll down and click Manage Attachments.

[mike7952]

If you can upload a example workbook with explanation of what you want will help you get a solution faster.

Please Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the [#] button at the top of the post window (if you are editing an existing post, press Go Advanced to see the [#] button). The result will appear like this in the post window:

Please Login or Register  to view this content.

[Mr. Rogers]

Sorry about that! I added the code tag and uploaded an example. It's got the long-form, working version of code in it currently.

[mike7952]

Give this a try. Don't think arrays are needed with what your doing. But if you would like me to I could give you an example with arrays.

Please Login or Register  to view this content.

[Mr. Rogers]

Worked perfectly! Thank you so much! But just for my own knowledge, is there a way to assign values to an array with a loop? For instance, if I have "cat" in A1 (want to assign it animal (1) and "dog" in A2 (want to assign it animal(2), is there a way to make a loop that says: loop 1 (i=1)--value in A, i = animal (i) and then for the second loop (i=2)-- value in A,i =animal (i) and then be able to use those assigned arrays further down in my code? Again, thank you so much!

[mike7952]

here is a couple of ways to do what your doing. For what your doing you don't need a loop to assign the values. See if this helps

Please Login or Register  to view this content.

[SPARKY347]

FYI the best intro to a post I have seen yet

[Mr. Rogers]

Again, thank you so much for taking the time to write this. I've just got one question: I defined

Please Login or Register  to view this content.

so that I can put a value in them (ex. J2= "H5" & K2= "K5")which will define the range that the data will be posted to on their respective pages.

Please Login or Register  to view this content.

With your code it posts to the next available row. Is there a way to use that "with" statement to paste to the values that I specified in J2 and K2?

And thanks Sparky! I figure if I'm asking for your help and taking your valuable time away from other things you could be doing I should at least try and inject a little joy and humor in it.

[mike7952]

Like this?

Please Login or Register  to view this content.

[Mr. Rogers]

Just like that! Worked perfectly! Thank y'all again for all of your help!