DateValue function presenting unexpected results in VBA

Hi folks,

I'd appreciate some help here. I've spent a lot of time reading up and understanding the challenges around dates in Excel. So, when I came across this challenge, I thought it would be an easy fix. A bunch of dates in a spreadsheet have become messed up because during a migration one or other of the systems had the wrong locale set causing dates in a UK format to be incorrectly recognized as US Dates. So now there’s a bunch of dates in the sheet, some correct because the high 'day' value seen as an invalid 'month' in US format made the date go in as text (Ironically these dates are correct as they look), but the low day values are recognized as months and form valid dates. For example, the date "7th of Jan" (7/1/2019) is in the sheet as July 1st, and stored as a proper date value i.e. 43647.

Fortunately, in the sheet there was a column whereby the correct month number was entered on its own so I figured I could do a 'DateFixer' macro that simply converts the dates stored as text into date values with a simple datevalue() function and for the cells stored as a date values, convert them to text, check against the column with the month number and swap the Month and Day in the dates that need it then resolve them back as dates values. Sounds straight, forward right?!

Well no. Cell A1 holds a date stored as a string (left aligned). I run the line of code:

Please Login or Register  to view this content.

Result, 43516, which when formatted as a date shows as 20/02/2019 (right aligned)

But, I put the same steps into a variable and...

Please Login or Register  to view this content.

changes nothing! Can anyone explain why? And indeed how do I use variable to get the results achieved with the literal reference to Range("A1")?

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. 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, and it also maintains VBA formatting.

Click on Edit to open your post, then 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

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)