Loop to Paste Values in a Different Column

[nevi]

Hey There!!

In looking around the Forums, trying to find my answer there, I think I have this narrowed down to needing a Loop, but I have never done a loop before and am not sure how to tie it in together with my project...

In a nut shell, at the point in time where the Loop would come into play I have just copied a value into the Clipboard. What I need the Loop to do is scan down Column A on the "Master Data" sheet looking for the date that is in Cell A1 on the "Data Load" sheet. Once it finds it, move over to Column E and paste.

For example, Lets say Cell A1 on the Data Load sheet has December 1, 2012 in it. The loop will look at each cell in the A Column on the Master Data sheet until it finds 12/1/2012. For the sake of example, lets say it finds that in Cell A14279. It would then move to Cell E14279 and paste.

Make sense?

Your help is really appreciated - thank you very much!

[nevi]

I see there are lot of views on this, but no responses...

I guess it is not as simple as I thought? Or maybe I just didn't explain it well? I came up with the below, but it gives a Type-Mismatch error and doesn't work, and even if it did locate the right row, I still have no idea how to get it to paste in Column E of that Row instead of Column A... Still, this is what I came up with:

Please Login or Register  to view this content.

The above did not work at all, but I just can't wrap my mind around how a Loop Structure works. I think a Do - Until command might work better here, but I can't figure out how to tell that one to stop, and still have the issue of how to move over to Column E before the paste...

I am really, really stumped here and not a clue how to go at this... Any help would be really appreciated. If the Loop is not the way to go, I am wide open to suggestions.

Thanks for your time and help!!

[nevi]

Okay, think I just about have got this figured out - I figured out how to shift the active cell right, but discovered that when it is going through the range doing to search, it is not actually selecting the cell. How do I tell it to do so?

What I currently have is:

Please Login or Register  to view this content.

Now, the above is kinda working, except that it is just moving 4 cells to the right of where ever I happened to have left the active cell. What I need it to do is select the cell in A:A that has the match before proceeding. I tried DateColumn1.Select, but that didn't work.

Any ideas?

[mike7952]

Can you upload a small workbook example of the actual layout of the worksheet your working with? And show the expected results.

[protonLeah]

Please Login or Register  to view this content.

+ You need to change your title to conform to the rules.

[nevi]

Sorry about that - I have read the rules and changed the title.

As per your request, please see the attached. Now, there is a bunch more in there that does not pertain directly to this. The part I am having the issue with is right down at the bottom. In a nut shell, an in the example I left ready to go in the attachment, you click the Command Button, it will scrub the data on that sheet and copys it. This is where the loop kicks in. It will then move over to the Master Data sheet and look for the Date that was placed in Cell A1 of the Data Load sheet (in this case, December 27/2012). The loop will look down Column A of the Master Data Sheet, find 12/27/12 in Cell A5618, move to Cell E5618, and Paste Special/Values.

Going back to protonLeah's post, I tried the "DateColumn1.Offset(0, 4).PasteSpecial Paste:=xlPasteValues" line you suggested, and it gives an error about Merged Cells needing to be the same size, which makes no sense at all since there are no merged cells... LOL No idea at all what went wrong there...

Please Login or Register  to view this content.

[mike7952]

What is to be pasted into column E?

[nevi]

If you are looking at the attachment, the Copy command happens on line Line 847. After the information is scrubbed, it selects the range I8:R55 and copies it. When the loop is running, that information is already in the Clipboard and is ready to be pasted.

[mike7952]

I still dont get what your doing? Attatched is a copy of the ws before getting to the entering the loop? I dont see the the logic why some of column G and H Look like they do. And why rows 8 thru 15 columns I thru R are zero'd out?

[mike7952]

Edit see next post #11

[mike7952]

Give this a try. Replace the below code into your button click event. Format Column H in Master Data Sheet to Percentage.

Please Login or Register  to view this content.

[nevi]

First off, thank you again for all your help. I really, really appreciate it.

I think maybe I have not explained myself well here, and the example I uploaded over-complicated things. You asked a few questions about Columns G and H look like they do and why some of the values are being zero'd out, but that part is actually doing exactly what I need it to do (has to do with filling in the blanks left by the multiple data sources that lead up to this point). As I said, that part is actually working perfectly and doing exactly what I need it to do.

My issue is coming after that point. Once it finishes scrubbing the data (adding all those zeros, etc), it selects I8 through R55 and copies the data into the Clipboard - here's where my issue begins. What I need it to do now is look at the Date in Cell A1, move to the Master Data sheet, and compare that date to the dates listed down Column A. Once it finds a match, it moves of to Column E on the matching row and does a Paste Special/Values.

The attached example is preset at the point where the loop would kick in and has all the other code (the stuff that is working) removed, so I think it should be a lot more explanatory than what what I uploaded before. This is what I've got:

Please Login or Register  to view this content.

I hope that makes more sense, and once again, thank you soooo much for your help.

[mike7952]

Did you try my solution in Post#11? Is that not the expected results to be posted to the Master data sheet?

[nevi]

Well, wouldya looky that!!! When you took my massive IF structire out, I thought you had removed that bit all together, which was no good. But I tried your code and now realize it is in there somewhere... LOL

I have no idea what your code is actually doing, but it works like a charm. You are a total lifesaver - thank you!!!

I am going to study this piece by piece to get a better understanding of what exactly you did. Thank you for all the comments as well - they are going to be a big help.

Thank you!!!

[mike7952]

Glad its working for you. I commentted the code so to give you some insight of whats going on. The part that replaces your massive if statement is the code below.

Please Login or Register  to view this content.