Matching patient personal data to that already stored in a database

I am trying to write a macro to transfer patient personal data onto a database.

On sheet 1 the user inputs the patient surname, forename, date of birth, date of test and the test results.

On sheet 2 is the database, where patient surname, forename and date of birth is stored in columns A, B and C respectively. The remainder of the columns on each row is available for scoring test results.

What I would like the macro to do is as follows:
• Read the patient surname as input on sheet 1
• Check on sheet 2 if that surname already exists
• If it does, check if the forename and date of birth also match to sheet 1 for that particular entry.
• If a match is found then copy the test results into a column on that particular row. E.g. could be copied into column D.
• If no match is found, then all the data is entered as a new row on the database (I’ve written the code for this bit but thought I should add it in for clarity)

This will then allow all test results for a particular patient to be read off the same row in the database.

I've been playing around with it for a few days but still cant get it to work. Any help offered much appreciated!

Helen

Thanks RoyUK, am downloading the example now - will let you know how i get on!

Helen

Ok, this is the code i have so far for checking whether the data from sheet 1 (data entry) matches an existing entry on sheet 2 (database). Not sure if this is the best way of doing it but it works!

Please Login or Register  to view this content.

The problem i'm having now is with the 'perform copy function as follows.

If the code finds the outcome of the above IF to be true, i then want to copy some data into column F of sheet 2 (database) on the SAME ROW where the code has found the match. I've tried using ActiveCell.row but can't get it to work - maybe this isnt the best approach??

Cann anyone help me with this?

Helen

Hi,

I have attached an example workbook with dummy data and modified my code for this dummy data.

The user would input the data on the "data entry" sheet and the results displayed in the "database" sheet. In my original document i achieve this using a button to activate the macro.

Hopefully this will explain better what i'm trying to achieve. After having identified a match between the information on the "data entry" sheet and an existing entry on the "database" sheet, i then want to copy and paste the test results onto the corresponding row on the "database".

Please Login or Register  to view this content.

Hi

The file you attached in the last post seems to be exactly the same as the original i sent you....

Helen

Thanks very much.
Will have a look at it and hopefully it will do the trick!

Helen

Thank you RoyUK for all your help.
I managed to edit the code you sent me for inclusion in my original workbook and it all works perfectly.

I didn't really know anything about VBA this time last week and have learnt quite a lot through getting things wrong!

Thank you again for your help!