Need Help Changing current coding

[trevor2524]

I have the following Coding that looks for certain values and check to see if the criteria is met.

Please Login or Register  to view this content.

In this portion it is checking to see if the Value in Sheet1 matches the Value in Sheet2. If it does its suppose to put a "Yes" in Column H. What I need changed is before it can put a "Yes" in Column H it needs to check Column F in that corresponding row and see if that date has reached. If it has then then Column H can receive a "Yes" If it hasn't then the macro ends.

Please Login or Register  to view this content.

Any suggestions?

[judgeh59]

try this

Please Login or Register  to view this content.

Untested

[trevor2524]

Kinda I added a little bit more:

Please Login or Register  to view this content.

The reason is it turns out that the cells from Sheet 1 and Sheet 2 match and but the Date in Column F hasn't been reached yet then it should update no Columns. But if the Dates from Sheets 1 and Sheet 2 do not match then it should still update Column G on Sheet 2. The Format for the date reached isn't working. I'm putting in Sept1 and its not recognizing that that date has passed and should mark Column H.

[judgeh59]

can you upload a sample worksheet?....kind of working in the blind here...there are a few questions that having the data would answer....

[trevor2524]

Sure

Test1b.xlsm

[judgeh59]

sorry for being dense....is the potential outcome WS what you expect the macro to do once it's completed?

[trevor2524]

Yes, it should come out how "Potential Outcome should look like for file 5800 and 60000. But say on Sheet2 you change Cell F20 to 9/5/2014 then file number 60000 should have no values in columns G and H when the macro is done. I dont knot how if columns E and F have a Workday formula put in the cells will affect anything or not?

[judgeh59]

but not for files 5802 and 7890....is that correct....I'm trying to follow the logic....

[trevor2524]

Those files require another macro that hasnt been developed yet so for this macro they will not be touched.

[judgeh59]

sorry for asking another question...

in the potential outcome WS you have a yes in G16 for file 5800...

the logic in your posts states that if Sheet1 column F (Date) for that file number which is 5800 and that date is 9/3/14 (forgot formatting right now) equals Sheet2 5800 date in column D AND the date in Column F is less then Today....but the date in sheet 2 for file 5800 is 8/31/14 and will never have a "yes" in either column...

what am I missing?

[trevor2524]

Yes the original coding states

Please Login or Register  to view this content.

So if the Date on Sheet2 does not match the Date on Sheet 1 and Column I has no Value then Column G will receive a Yes.

For 60000 The dates on sheet 1 and sheet 2 match so it will trigger the next if statement. it will check Column F to see if that date has been met yet if it has then Column H will receive a Yes. If the dates on sheet1 and sheet2 do match and the date in Column F has not been met or passed then no Columns will receive a "Yes" which will happen if you change the date to 9/4/14 for Cell F20. Hopefully this helps explain exactly what I'm trying for.

[judgeh59]

I hope I'm closer ... for some reason it's taking me a bit to get my head wrapped around the logic....but give this a try....

Please Login or Register  to view this content.

[trevor2524]

It seems to be working the way I need it to. I appreciate all the help. Since you have an understanding for this would you like to try and help me with the next portion. Regarding the other two files on that sheet?

[judgeh59]

I wouldn't mind at all but I just had project dropped in my lap...if you can wait until tomorrow I can help....

[trevor2524]

Not a problem I'll message you the details if you want.

[judgeh59]

work for me....

[judgeh59]

I had to cheat a bit, hopefully it won't be to much of a problem....I added duplicate numbers on sheet2 for the file numbers....it made it easier to determine what goes where...I also adjust your WORKDAY() formulas to allow for blank data....give it a go and let me know....

MODS: I was requested to add some more code to the original VBA Code. I update this OP (even though it's set to SOLVED) because this where it started. Hopefully I'm not violating the "Must start a new thread" rules....please advise

[trevor2524]

Thanks for the update. It almost works correctly. Since you have to duplicate the file numbers can we do it a different way. Because right now it it finds a file number it will continue with the whole process and end up filling on MIA2 or 3 if those ones havent been met yet. The reason for the differnent MIA's is because this is updating a much bigger sheet and it could be pulling files from 6 months ago that were once declared MIA and now are MIA again so it will need to be updated.

So my idea for the file numbers is a follow:
I asked for it to check Column I and to see if it equals the value of "Yes". If that value is found its suppose to go to the next row. It should go back to Column A and copy the file number to the cell right below it. And then rerun the previous Check to see if the Value of "Yes" is in this new rows column of I. If no value is found it will go to Column D and see if it is empty just like how you had it. If there is a value of yes then it will re run the previous section. And then it will continue on with macro. This way it only adds file numbers to the MIA's that are being used.

Also it was working on the last one you sent but on this one it is not.The Possible MIA Column should only equal yes if Column I has no Value and Column F's Date is reached or has passed based on the date the macro is run.

Hopefully this makes sense I'm just writing down ideas that are popping in my head regarding this macro. I do appreciate all the help.

[judgeh59]

Hi Trevor;

I keep trying to get back to this and it looks like I'm not going to be able to....you should probably start a new thread with the updated requests....sorry it took so long....

[trevor2524]

Thanks for the help I appreciate it. I'll see if I can get more ideas from a new thread. Thanks again.

[judgeh59]

I'm glad I could help a little....have a great day