VBA If Statement For a Range Of Changing Values

Voy

New Member
Joined
Jan 12, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
I am a novice and I appreciate any assistance! I have a list of Doctor Specialty Codes numbered 1 through 130. Each number represents a particular specialty for example 1 is General Practice, 2 is General Surgery, etc. Whichever number is in cell A1 through cell A130 should display it's corresponding specialty in cell B1. I don't know which number may be in cell A1 because I get it from a changing data set with different Doctors. I tried using IF statements as shown below:

Since I don't know what number will be in A1 through A130 I have to include all numbers 1 through 130 in the code and this makes the code too big and inefficient. Can someone please show me how to efficiently do this. I think I should loop it but not sure how to do that. Thank You!!

Sub test()

If Range("A1").Value = 1 Then
Range("B1").Value = "General practice"

End If

If Range("A1").Value = 2 Then
Range("B1").Value = "General surgery"

End If

End Sub
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Voy

New Member
Joined
Jan 12, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Thank You!! I will review the Vlookup link you provided.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,934
Office Version
  1. 365
Platform
  1. Windows
You are welcome.

Post back to this thread if you have any issues implementing it. If you provide the details and your formula attempt, we should be able to help you figure it out.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,677
Messages
5,637,735
Members
416,981
Latest member
PLonchar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top