Macro shows incorrect result

Good Afternoon everyone,

I'm trying to figure this code to fix the following issue. So this Excel macro has 3 sheets (Old, New, and Results), it works by adding an old report to the "Old sheet" and adding
a new report to "New Sheet". When I run the macro it shows me New orders, Revised Orders, or cancelled on the "Results sheet" and is color coded however I came across an error. If you look at order # 90946 on the second row it shows that is a New order. It's supposed to be revised or "Version" (Yellow) because the version changed from 1 to 2 based on the Old sheet and New Sheet. If you look at order # 99047 on the Results tab you know that it's correct because it went from version 8 to 9. I don't know why order 90946 shows that is New, it's supposed to be a version change from 1 to 2.

I've attached the excel macro and VBA code is below. Please and thank you.


VBA Code:

Please Login or Register  to view this content.

Welcome to the forum. Unfortunately, you have not observed two important rules that govern this forum.

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.
------------------------------


Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Hi fsalas2006,

I suspect that it is because by the time you get to the point where your comment says "Clean up Added Information" the value in cell K2 shows as #REF

I wasn't watching closely enough as I stepped through the code to see exactly where it went wrong, will have to try again and pay more attention.

I did notice however that when you 'Copy all sheets into Results' that you are selecting an extra blank row, not sure if you mean to do that or not?? (your f & s counters are already +1 when you leave your do loops

ps... welcome to the forums, please take the time to do as @alansidman suggests, read the forum rules to make it easier for the volunteers to help you

Hi fsalas2006,

It looks like the offending code causing the #REF is this section.

Please Login or Register  to view this content.

I am not at all sure why you are doing this in the manner that you are, ie delete a heading row and insert another immediately after it. Maybe you should simply copy & paste a range rather than delete & insert but I guess you know your logic and what you are looking to achieve

I havent gone any further with testing the final outcome