VBA Loops

Hello,

I am having a lot of trouble figuring out this script. I need to move some cells around on a very large spreadsheet.

I have 5 columns.
I have items, 2 columns in between, data and a new column for data

Item #_____Col 1__________Col 2 ________Data ________________new column data

Item1____ 798-3A01 ____ No Value______SANITARY WATER____*insert 51.00 here_
Item2____ 798-3A02 ____ No Value______ 100.00____________________
Item3____ 798-3A03 ____ No Value ______51.00____________________


I want to take the data for Item3 (51.00) and cut and paste it to the new column for data on the same row as Item1 (would be next to SANITARY WATER). I have to do this almost 10,000 times.

Seems easy, except the Item # list is not consistent. Sometimes it skips numbers, so I have to include Do Until loops to find the row Item1 is on, and then tell it to go to the right to the new column for data and paste.

Here is my problem: The Do until loops keep going up to the row with Item2, not Item1. Something I am telling it to do is making it go to just below where I need it to be. So then it goes right like I want it to, pastes in the right column, wrong row.

My code then goes back to find the 'Item3' cell and goes one down so it can reiterate the script on the next item. It uses the exact same loop and it does this correctly.


Here is my code:

Please Login or Register  to view this content.

Loops are very ineficient, add that to the fact that your code is constantly selecting cells & it could potentially be extremely slow. I would use AutoFilter, automated with VBA.

Attach an example workbook