Compare two date ranges and copy unique descriptions found in matching range

acirE

New member
Joined
Nov 12, 2020
Messages
2
Reaction score
0
Points
0
Excel Version(s)
O365 ProPlus Version 1908
I received a spreadsheet to be used for recording time for payroll. One sheet stores the description, date, and hours spent in 3 respective columns, this is a an ongoing record which can have any dates in it. The second sheet has 2 date ranges (week 1 and week 2 of the current pay period) and uses a Macro to scan the first sheet and copy all unique descriptions for that date range over to the second sheet. It then uses various SUMIF formula's and such to create the rest of the time card. It successfully completes this for days of the month 1-9, but when it hits day 10 it no longer copies the descriptions over. If this problem occurred at 12 I would assume its a date formatting issue, but what would cause it to work for day 9 of a month but not 10.
 

Attachments

  • Time Card.xlsm
    42 KB · Views: 15
Hello acirE

First thing I did when looking at your file was widen the date column in order to see what the dates were.
Then running the macro wondered what the issue is as other than a missing formula in C18 everything looked ok to me.

Try adding this autofit line and see if that helps
Code:
    shTimeCard.Range("payPeriodWeekOneProjects").ClearContents
    shTimeCard.Range("payPeriodWeekTwoProjects").ClearContents
    shTimeLog.Columns("B").AutoFit

Hope that helps.
 
That fixed it!

That did the trick! Thank you!
Is there any chance you can tell me why this single line worked? Autofit just changes the column widths does it not? Why would this change the value comparison? Odd the columns needed to be widened to see the dates, on my screen they appeared in full just fine.


Hello acirE

First thing I did when looking at your file was widen the date column in order to see what the dates were.
Then running the macro wondered what the issue is as other than a missing formula in C18 everything looked ok to me.

Try adding this autofit line and see if that helps
Code:
    shTimeCard.Range("payPeriodWeekOneProjects").ClearContents
    shTimeCard.Range("payPeriodWeekTwoProjects").ClearContents
    shTimeLog.Columns("B").AutoFit

Hope that helps.
 
Nope, can't explain it other than experimentation showed it to work.
 
Try changing all instances of xlValues to xlFormulas then it won't matter about Autofit.
 
Back
Top