Need Help Solving a problem with a Code

I have a VBA Code that allows the user to select a date on a drop down menu, and when they click the submit button it asks them if they have the correct date before proceeding. When they hit ok it executes the script and copies data from each row into each sheet according to information found in column A.

The problem that I'm having is that the operator needs to be able to pick any day of the year in order to back fill data, this spreadsheet will be rolling out in April, however the yearly data sheets show every day of the year since January 1st. When setting the date in the drop down menu the script cannot process a date more than a few days past the first available date in the other sheets.

For example:

If the yearly data sheets all start on January 1st and January 1st is the first available date, then I can enter the 1st and 2nd without issue, but if I enter in a random date from the year, for example today's date, March 27th the code fails and the coded error message appears.

Is there someone who can help me to modify the code so that it will copy the data over to whichever date is selected, regardless of which dates are empty on the yearly sheets?

Here is the entirety of the code:

Please Login or Register  to view this content.

Using Excel 2010

Please let me know if I need to post additional information such as Screen Shots etc.

Thank you.

A copy of your sheet would be very helpful in solving this.. But I have found the .find to be a bit funny with finding dates.. You might try formatting your "MyDate" into a number before running the search code.

ie.

Please Login or Register  to view this content.

Not sure this will be much help but worth a try

Thank you both for replying so quickly.

I tried the first suggestion made for edit to code, but every time I tried to click the button it just produced an error within the script.

I will try to input the mydate change in and see how that works.

I'm honestly not very good at VBA, so I'll do my best!

What would be a good way to attach the sheet? Screenshots, or is there somewhere I can upload the sheet so that it can be downloaded and viewed?

I've created a dropbox and uploaded a copy of the document as it is. You'll notice that it works fine when entering dates that are within the earliest available, but it doesn't do well with advanced dates.

https://www.dropbox.com/s/m1zglkm2pv...heet.xlsm?dl=0

This is definitely making progress, I'm no longer getting an error code when I press the button. It still works when entering earliest dates, however when I enter a random date, for example March 17th or july 26th, the button will execute, but no data will actually copy over to the other sheets.

Try setting "MyDate" to the same format as the dates on your data sheets

ie.

Please Login or Register  to view this content.

Put it into Module2

Originally Posted by unit285

After looking at your sheet I changed a bit of formatting. See if this works for you

Sorry for the delayed responses, thank you everyone! This last code is a perfect fix so far. I have run a few tests and every result so far has been perfect, appreciate everyone's help.

Originally Posted by gmr4evr1

Glad we were able to get it working for you through collective effort

Yes, thank you very much, all of you.

Glad to know its working for you