Extract information from 2nd workbook and record in current workbook. VBA

Hi there,

I am trying to come up with a macro that will ideally help me find information easily. I have attached two workbooks.

The Current File DIS is the file where the macro should go, and the Master Transfer file is the file to extract information from.

What I would like to do:

1) Search each record found in the Current File DIS against the Master Transfer file and find matches.
2) If a match is found, (matching conditions are matching MRN and ADM_Date), have the macro record what happened to that patient based on the Master Transfer File in the "transfers Details" column.

For example, if a matching record is found, I would want this macro to extract what's in the "eff_date, PCU out and PCU in" from the Master Transfer file and record all of that in the "Transfers Details" column for THAT patient. I have put some dummy records in the "Current file" for illustration. Both 12345 and 74463 are patients found in the "master transfer" file. The "Transfer Details" comes from the "master transfer file".

If no matching record found, move on. (no transfers for that patient), or just put "None" in the "Transfer Details".

The difficulty for me is putting what can be multiple lines of info into a single cell.

Is anybody able to assist me on this one?

Thanks very much

Anybody able to assist?

anyone able to assist?

Thanks

Nice....I tested it and it worked!

So what's in the Find() are pretty much all standard parameter settings in my example?

and am I correct for understanding this logic?
In the Find() code segments, this is where we first find the matching MRN. Then, if the matching MRN has been identified, we look for matching adm_date, which is that big If statement. Correct?

Thanks

Not every patient has a maximum of 2 rows. They could have up to 10 rows, depending on how many transfers they have over a period of time.

I think this doesn't matter right? The code looks for, in the found cells, all the necessary informations.