Issue with some numbers during VBA lookup, What should I look at?

I have a strange issue going on that I'm hoping someone might give me ideas on where to look to fix it. I can't post the actual document, so I've attached one that does the same thing as what I'm doing. (It works correctly in my example)

I have a spreadsheet in calendar format that performs look ups using the days date as one of the references. It seemed to be working great except I just noticed an issue with certain dates in October. The dates are all coming from a query from the same source, and there are thousands, but I'm seeing just a few that are not populating as they should. They are the same format as the working numbers and I double checked with ISTEXT and converted them an extra time in Power Query just to be sure. If I manually add a new line with the same employee/date it doesn't work, but I sometimes when I add another employee with the same date, that date will work for them. Oddly, if I add more dates in October to the end of the data, it will suddenly populate two more of the October dates (but not the ones I entered). I tried adding 6 to see if I'd get more but it didn't even populate the two it had before.

I know it's a long shot without being able to see the actual issue, but if anyone has any ideas for things to check, it would be greatly appreciated. There are thousands populating correctly, I just can't figure out what the deal is with these.

Anita Knapp,

Your code is limitting dates between Sheet1!F3 and end of that month.
So if you have dates in multiple months, it fails.

try

Please Login or Register  to view this content.

Hi Jindon,

I've just tried this and it runs but nothing populates.

I'm not sure if I understand what you're saying. There are multiple months in the data but each sheet being read only has one month.

OK,
I didn't know if the dates in sheet1 is just one month.
However, your code is assuming data sheet MUST have the record that have date in Sheet1!F3. i.e Oct 1st 2023.

Just change data!G13 from 2023/10/1 to 2023/10/2 and run your code.

Code in post #3 runs fine for me.

I must have something different in the real sheet, as this doesn't pull up anything either. Your explanation makes sense though. When I use the code I had and add all dates to the data, it works. If I have to, I'll just have some dummy data in the background. Thank you!

If you are talking about different data, no way to even guess...

I think you did though, adding those dates fixes it. Is there any way to tweak the existing code to not require it? Along the same lines, is there a way to add something to the existing code that highlights the cell if there is more than one row that matches the ID and date?

Thanks!