How to change a word/number to another word automatically using VBA

[fmorenojr]

Good morning, everyone,

I am working on a spreadsheet for my manager. We use a number system for locations that we have in our organization. What I am trying to do is once the number is added into a cell, it would automatically change to the location. I tried looking for a code online but maybe I wasn't as descriptive in the search bar as i was not able to produce results.

For example, if someone puts "02" in any cell under D, it will change to "02 - Storefront." if someone puts "03" in any cell under D, it will change to "03 - Call Center" and so forth. I have 14 locations that I have to add. I am not sure if I VBA used would house all the locations or would the VBA be used individually for each location.

any assistance is greatly appreciated.

 

[rlv01]

I set up a test sheet and a worksheet "Locations" with a lookup table and the code worked for me.

1. What line generated that message?
2. Do you really need to format col D as text? If the idea is for users to simply be able to type in a number 1,2,3 and have it be replaced with a location string, why complicate things by making them type in "01" or "02"? It would be simpler to just use numbers 1,2,3, etc and search for them. @6StringJazzer's code works fine for that.

Book3
	A	B
1	2	02 - Storefront
2	3	03 - Storefront
3	4	04 - Storefront
4	7	07 - Storefront
5	8	08 - Storefront
6	16	16 - VEC
7	19	19 - Call Center
Locations

Cell Formulas
Range	Formula
B1:B7	B1	=RIGHT("00" & A1,2) & " - " & C1

 

[6StringJazzer]

If the idea is for users to simply be able to type in a number 1,2,3 and have it be replaced with a location string, why complicate things by making them type in "01" or "02"? It would be simpler to just use numbers 1,2,3, etc and search for them.

That is true, but I was taking the OP literally when it said "type in 02". It could just as easily use integers.

 

[fmorenojr]

Seems like it was user error on my part. I created the sheet as locations, but never created a lookup table. thank you both 6StringJazzer and rlv01 on your assistance on this on.

​