Need INDEX MATCH macro to loop step 6

I have spent the last couple weeks writing several macros to speed up a report. I am stuck on the last part which is getting the final data.

I created a INDEX and MATCH macro which does give me the correct data for my test data but I cannot figure out how to make it loop to fill the remaining employees. The macro compares dates on 2 sheets and for the matches, it offsets and adds the formula. This is the only way I could think of to give me the starting cells. I need the loop to step 6 but I cannot figure out how to modify my code to do so. I appreciate any help.

Here is my macro...

Please Login or Register  to view this content.

xladept,

Thanks for the help. I used the code on my actual test data of 1000 rows and it worked great. I did have another sheet of similar data that I did not include because I thought formulas would still be used and I could replace the old formula with the new in the code I receive from this site. I changed the columns to the new sheet but after it fills one column, I get a "run-time 91: Object variable or With block variable not set" error. I reattached the workbook with the missing sheet. Thanks again.

Error at line:

Please Login or Register  to view this content.

MarvinP,

Thanks for looking at this. I see where you are going and I like the idea but it will not work in this case. I have macros just to clean up the data the way it is. Also, this is for someone at work. She was manually looking at 100s of rows to enter the numbers by hand. The original report is so messed up that I had to create code to remove colored rows, duplicates, and merged cells just to name a few. With this code, the report can be done with all code. Now she can paste the data in and run the macro and be done. This will knock down a 3+ hour job to a simple click. Excel is great. Thanks again.

sktneer,

Nice catch on the incorrect year. I do not know if it was given to me this way or if I accidentally changed it myself. I knew the error was sometimes caused by not finding a match and with the year not showing, it was easy for me to miss. Thanks for the help.


xladept,

Thanks again for the code. I like this way compared to using formulas that I was before.

xladept,

Thanks for taking the time to create that code. I tested it on my full data and it worked.