Problem with dates taken from Excel 1

[LizF]

Hello again...

My code picks up a date from a cell in one Excel file and compares it with one from a cell in a second Excel file. My problem is that while it works fine for one file, it returns the wrong date from the other. It's nothing obvious like 31/12/1899, and I think I'm picking up the correct cell (reference thread about using a row and a range to pinpoint a cell to copy).

Any ideas?!!
Thanks,
Liz

 

[andrew299]

Could oyu pop a bit of your code on here to have a look at?
Also does the date that it does return exist anywhere else on the sheet?
Can you tell us what cell you want in file 1 and what cell you want in file2. This will tell us if the code you have matches your requirements.

Andrew299

 

[LizF]

Hello Andrew299, and thanks for the response.

I think I've found the cause of the problem , it was in fact the way I'd referenced the cell; it was picking up the wrong one!

Just goes to show that you can't be too cavalier with using named ranges!!

Thanks again,
Liz

 

[andrew299]

You may already know but in the vba window when you have stepped into a code, by placing the mouse cursor over your range code line when in the same module a little yellow box will appear and will tell you what cell it is referencing - makes checking a bit easier
Also works for boolean variables, integers etc.
andrew299 It may have hit every branch on its way out of the ugly tree, but hey! It works. (sometimes)

 

[LizF]

Cheers Andrew, I had actually only sussed that out yesterday, so it's a point worth making, hence the star!

The problem in my code was the the "CurrentRow" variable I had defined and passed to the second function referred to the real row reference i.e. in the spreadsheet as a whole. Within the second function I was using it to specify a row within a range, however the range doesn't start until row 9 so there was a mismatch which caused it to look lower down in the range than I had intended. A cautionary tale!!

Thanks again,
Liz