VBA-Perform lookup for all rows in column if date selected?

Thank you so much for replying! I've been working on this for a couple weeks and it's driving me crazy. I've updated the code and file.

I think I fixed the Lookup1 and Lookup2 definitions but I'm not sure if I have the coding for that correct. The goal is if the user selects F5-O5, then it starts working in cell F6, doing a look up of the value of E6 and date in F5.

Res, Day, and time are columns H, I, J in the data tab.

I have the dates repeated in 3 and 5 because I was having issues with the formulas if they didn't have the TEXT conversion. Are you saying that I can just have my formatted date and it will read it?

Presently, the source files load with 70,000 rows and is filtered down to 22,000 rows in the data tab that is being used for this. This will double by the end of the year.

Thank you for your looking and tips is greatly appreciated!

Please Login or Register  to view this content.

I've just updated it with what it would look like if 9/3 and 9/4 (J5-M5) were selected when the macro ran.

Thanks!

Originally Posted by JohnTopley

Please add some manually-calculated results to your workbook.

Thank you so much for doing this! When I copy this over I'm getting a mismatch at idy = Application.Match(a(r, 1), IDrng, 0) . I rearranged my data and month sheet to match columns and rows so the only thing different is the sheet name that I've changed. I know it's tough without seeing the workbook, but any ideas on what it could be?

It sorts my data and activates my month tab before it errors so I think its looking at the correct sheets. I thought I could figure out the issue by crossing the data and month sheets for both, but the X1 month with my data sheet and my month sheet with the X1 data both give the same error.


I know you put what these items are but I don't think I'm understanding a few of them. (Probably more than that but these are the ones that I'm hoping I can catch my error on)

Does (R,1) represent the IDs in the first column of the data range?
Is the sRow the row that the dates are on?
Is the nRow getting the total number of rows to complete from the date column of the data sheet?

Thank you!

Ok, thanks! I'm going to keep at it until I figure out what I have wrong here.

Thank you for taking the time to put this together, it's greatly appreciated!

I was thinking it might have been something I had set in my workbook so I've made a new test book. I'm getting the error in the same spot. I've attached the new book, are you able to see what I'm doing wrong?

I don't normally jump in on solved threads (it isn't is it?) or when someone else has done all the hard work (so apologies to John).

Your problem is that you are trying to match employee codes from one sheet in another and they don't exist where you are trying to get an exact match.

You need to handle this error in what is an appropriate way for your application (goto 'next r' maybe?).

It works! This is awesome, thanks!. Is there a way for it to only error if the ID on the month sheet isn't found on the data sheet? I have hundreds of ID's but if not, it looks like On Error Resume Next will work.

This is perfect, thanks so much!