Loop & Array (Probably Simple!)

Hi Guys,

I have the following code which is designed to simply find and replace the values in a given range with values in another range. The find values are defined on a sheet in one range (column) and the replacement values in another. eg

I am assigning each other those ranges as arrays in the formula. The code needs to run through array1 value 1 and replace it with array2 value 1

I'm figuring there are two options here: Either define it all as one multi column array (as the two data sets are next to each other) or define them as separate ranges as I have (this method would seem to be good if the array ranges where in different locations)

Formula:

Please Login or Register  to view this content.

This is the section of code causing issues so far, I think my issues is I'm not coding the "i" variable correctly? Is there a simple fix here?

Formula:

Please Login or Register  to view this content.

Thank you

Not sure but somehow your oldlist and newlist arrays are actually taken as 2 dimensional arrays by VBA. And first and last element of array are empty (looked at Locals window)
Below code is working but you have to stop the loop one step earlier (as i value is increased by 1 in below step and last element is empty)

Please Login or Register  to view this content.

Great work guys all fixed,

I knew it was something simple I was missing. I have kept both methods above:

haripopuri; I modified yours as instructed with

Formula:

Please Login or Register  to view this content.

to fix the error once it gets to the final record. Also I release this is a good method for larger arrays?, i.e I can define a single name list and then use

Formula:

Please Login or Register  to view this content.

etc to define what column of the array.

TMShucks
Your transpose method will also come in handy!

thanks Again!

Originally Posted by B_B

Also I release this is a good method for larger arrays?, i.e I can define a single name list and then use

Formula:

Please Login or Register  to view this content.

etc to define what column of the array.

Yes. You can use multi-dimensional arrays (upto 60 dimensions for that matter)