VBA code to look up Physician Number on 2nd worksheet, based on type

[thatgirl100]

Hello,

Let me see if I can explain this clearly.
I have an excel workbook containing many different worksheets. On the worksheet "Reports", there are a series of VLOOKUP commands that grab values from other worksheets based on the Physician Number in cell 'C3'.

In a 2nd worksheet "Physicians", column A contains the physician type (e.g. Doctor, Nurse), and column B contains the physician number.

Column A Column B
Doctor 1001
Doctor 1002
Doctor 1003
Nurse 2001
Nurse 2002
Nurse 2003

I have created a command button on the worksheet "Reports" called 'Print by Physician Type'.

What I want to do is have code behind that command button that will look at the worksheet "Physicians", look for 'Doctor' in Column A, grab the first physician number in Column B that corresponds to it, paste it in cell C3 in the worksheet "Reports", and have it print the information on that worksheet. I want it to then loop through each physician number with a Type "Doctor" and print each time (since all the values change accordingly).

I'd also like to have a prompt, so that it will ask me "What Physician Type Would you like to print?" so that I can print all the reports for Nurses, etc.

I'd really appreciate any help on this matter as I am very new to VBA code.

Thanks,
Kirstin

 

[PHV]

as I am very new to VBA code
The macro recorder and then the F2 and F1 keys in the VBE are your best friends.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fumei]

Try recording a macro with the first steps of what you want to do. Take a look at the code. You are going to probably need to do some looping, so look up looping in Help. Hopefully, you DO have VBA Help installed. It is not installed by default. If you don't, get it installed. As PH mentions, it is one of your best friends.

When you have something concrete that you are working on, and need some suggestions/help, post your code here.

Gerry
My paintings and sculpture

 

[firefytr]

I think you'll best be served by creating some sort of UserForm where you can click a command button to launch it (or have a custom menu or toolbar with this option on it) and be able to specify whether you want to print all Physician types, certain Physician numbers or any combination therein. Post back if you need more help.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[Rofeu]

Hi,

I think a query would be very suited for your purpose.

Data --> Import External Data --> New Database Query

then select 'Excel Files' and your workbook.

Make the output your column with the ID's and put in a parameter on for instance a cell with validation with a list of the possible types to choose from.

You can turn on the macro-recorder while doing this to get the query in VBA

Cheers,

Roel