Find and Replace across multiple worksheets?

[Sandman13]

I have a workbook with two sheets, "Patients" and "Prescriptions." Prescriptions is the primary worksheet that contains all the info I need.

due to a normalization issue, I created a new record in my primary sheet, and it created a duplicate record of each patient I selected. So I've got multiple records for the same patient in my "Patients" sheet.

I want to find replace everything in the "patient_id" column in "Prescriptions", to match up with the actual Patient name, not an ID number... rather than performing a find and replace one-by-one... Anybody know how to accomplish this?

I've attached a sample of my sheet so you know what I'm talking about. The actual sheet contains over 5000 entries so a way to automate this process as much as possible is ideal.

[jeffreybrown]

Hello & Welcome to the Forum,

How about on the 'Prescriptions' tab in cell E2 and copied down...

=VLOOKUP(B2,Patients!$A$1:$B$7,2,0)

[jindon]

Try the attached.

[jeffreybrown]

Hi Jindon,

If have never seen this use of 'Static flg As Boolean', but then again VBA is not my strong suite.

How exactly does this work? Is it basically within one session that it store a true the first run and then flips to false the next time run?

How does it get stored as a variable a held? I thought usually once a macro was done the variable was dumped?

[jindon]

It holds the value within a procedure until you close the file, when the variable declared by Static statement.

e.g

Please Login or Register  to view this content.

[jeffreybrown]

Ok I see now. Thanks for the clarification.

[Sandman13]

Hey, thank you! the VLOOKUP function worked perfectly. I used "False" instead of the 0 and it worked the way I wanted. Thanks again guys!

[jeffreybrown]

You're welcome…glad you have a workable solution and thanks for the feedback...

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[amankmr17]

Just "group" the various tabs you want and then do the Edit/Find. keep hitting Find and note how Excel scrolls thru each tab.

To "group" tabs, hold down shift while clicking on each tab name...

Thanks,