Finding todays date in column A then select adjacent cell

[L plates]

Hi All,

This may seem simple and most likely is, but I am having a lot of trouble finding todays date in column A then selecting the adjacent cell to the found date to allow data to be pasted in. The macro is run from a form control button on a daily email tally sheet (GI Form Temp.xlsm). The macro should copy figures from the tally sheet to the master stats sheet for the current year (2016 Global Inbox Stats Sheet.xlsm). I get a run time error 1004 at line 85 in the code on Sheet1(GI Count) and am pretty sure this is due to the format of the date on the master stats sheet in column A but can't seem to figure out why....or....it could be the code that searches for the date!

I have attached both the tally and master workbooks. The section of code I'm having trouble with:

Please Login or Register  to view this content.

There are plenty of other issues with a lot of my code(!) which I will get to, but the find date and paste issue is my bugbear for the moment.

Can anyone please assist me?

Many thanks,

[TMS]

Please Login or Register  to view this content.

Regards, TMS

[L plates]

Thanks for your prompt reply TMS.

I have run your code on the 2016 Global Inbox Stats Sheet and got an empty message box. I amended your code with the following but the cell is not being selected.

Please Login or Register  to view this content.

What am I missing?

Many thanks,

[TMS]

What am I missing?

That would be data in the cell to display

Please Login or Register  to view this content.

[L plates]

Hi TMS, thanks again!

I can see how your code will give a message box and understand that a lack of data in the cell will give nothing to display (fundamentals!). My issue is that I'm trying to select a cell as per the below amendment but do not get a found date from the code.....until.....I have found that by changing the cell data on the test sheet (2016 Global Inbox Stats Sheet) to an actual date rather than a date derived from a formula, I am getting results.

Please Login or Register  to view this content.

Is there a way to get the code to work based on a date derived from a formula (my current cell format as per the test sheet) or will I have to use actual date syntax? I'm hoping I can use what I have otherwise I will need to re-think this!

Many thanks,

[TMS]

The code is designed to display the contents of the cell next to the cell matching today's date. It isn't intended to select the cell, given that you rarely need to select anything in order to manipulate it. If you want to select the cell, you will need to activate the workbook, active/select the worksheet, and then select the cell itself. You have all that information, so you should be able to do it.

Please Login or Register  to view this content.

Very slow and inefficient though as, no doubt, you'll then need to select the original workbook to do something with the data.


Not really sure what you are asking regarding dates and formulae. The dates in column A of the 2016 worksheet ARE derived from formula. Row 32:

Please Login or Register  to view this content.

= 03 February 2016

[TMS]

Thanks for the rep.


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[L plates]

Finally got it! All the code needed to be in this workbook, not in the sheet itself. That must have felt like you were extracting teeth!

Thank you very much for your perseverance with me and for the useful solution. Now on to the other issue with my code - another thread for that!

Regards,

[TMS]

You're welcome.