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]

Like most things, there are a number of ways to accomplish that. The simplest is probably to use the Worksheet Change event and hard code the definitions. An example would be

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Target, Me.Columns("D:D")) Is Nothing And Target.Cells.Count = 1 Then
        Application.EnableEvents = False
        Select Case Target.Value
        Case 1
            Target.Value = "01 - Warehouse."
        Case 2
            Target.Value = "02 - Storefront."
        Case 3
            Target.Value = "03 - Call Center."
        End Select
        Application.EnableEvents = True
    End If
End Sub

You would fill out the rest of your 14 location code definitions. The sub is located in the worksheet code module.

 

[6StringJazzer]

On one sheet you should create a lookup table with the number code in column A and the full expansion in column B. I will call this sheet Locations. That way you can change the codes without having to update code.

If a user types "02" Excel will interpret it as 2, unless you have formatted the column as Text. You might want to do that for both column A of the Locations sheet and the column where you are entering the codes.

Private Sub Worksheet_Change(ByVal Target As Range)

   Dim Cell As Range
   Dim Found As Range
  
   For Each Cell In Target
  
      If Cell.Value <> "" And Cell.Column = 1 Then ' column A
         ' Find the code
         Set Found = Worksheets("Locations").Range("A:A").Find(what:=Cell.Value, lookat:=xlWhole)
         If Found Is Nothing Then
            MsgBox "Code " & Cell.Value & " not found"
         Else
            Application.EnableEvents = False
            Cell = Found.Offset(0, 1)
            Application.EnableEvents = True
         End If
      End If
  
   Next Cell

End Sub

$scratch.xlsm
	A	B
1	02	02 - Storefront
2	03	03 - Call Center
Locations

If you want to make this a little fancy you can use Data Validation lists for data entry rather than typing in the numbers.

 

[fmorenojr]

On one sheet you should create a lookup table with the number code in column A and the full expansion in column B. I will call this sheet Locations. That way you can change the codes without having to update code.

If a user types "02" Excel will interpret it as 2, unless you have formatted the column as Text. You might want to do that for both column A of the Locations sheet and the column where you are entering the codes.

Private Sub Worksheet_Change(ByVal Target As Range)

   Dim Cell As Range
   Dim Found As Range
 
   For Each Cell In Target
 
      If Cell.Value <> "" And Cell.Column = 1 Then ' column A
         ' Find the code
         Set Found = Worksheets("Locations").Range("A:A").Find(what:=Cell.Value, lookat:=xlWhole)
         If Found Is Nothing Then
            MsgBox "Code " & Cell.Value & " not found"
         Else
            Application.EnableEvents = False
            Cell = Found.Offset(0, 1)
            Application.EnableEvents = True
         End If
      End If
 
   Next Cell

End Sub

$scratch.xlsm
	A	B
1	02	02 - Storefront
2	03	03 - Call Center
Locations

If you want to make this a little fancy you can use Data Validation lists for data entry rather than typing in the numbers.

On one sheet you should create a lookup table with the number code in column A and the full expansion in column B. I will call this sheet Locations. That way you can change the codes without having to update code.

If a user types "02" Excel will interpret it as 2, unless you have formatted the column as Text. You might want to do that for both column A of the Locations sheet and the column where you are entering the codes.

Private Sub Worksheet_Change(ByVal Target As Range)

   Dim Cell As Range
   Dim Found As Range
 
   For Each Cell In Target
 
      If Cell.Value <> "" And Cell.Column = 1 Then ' column A
         ' Find the code
         Set Found = Worksheets("Locations").Range("A:A").Find(what:=Cell.Value, lookat:=xlWhole)
         If Found Is Nothing Then
            MsgBox "Code " & Cell.Value & " not found"
         Else
            Application.EnableEvents = False
            Cell = Found.Offset(0, 1)
            Application.EnableEvents = True
         End If
      End If
 
   Next Cell

End Sub

$scratch.xlsm
	A	B
1	02	02 - Storefront
2	03	03 - Call Center
Locations

If you want to make this a little fancy you can use Data Validation lists for data entry rather than typing in the numbers.

I tried following the steps you provided, but it seems I am doing something wrong.

1. I created a new sheet and named it "Locations". Under column A it has the "codes", under column B it has the "full name of location." this is sheet 9.
2. I made sure that the location sheet that both colums are set for "text" and not "number."
3. I added the VBA code under "thisworkbook".
4. I went to sheet 2 and sheet 3 under column D and typed "2" and not thing happened. This would be the only two sheets where this change would be applied to.

not sure what I am doing wrong.

 

[rlv01]

3. I added the VBA code under "thisworkbook".

The code @6StringJazzer posted was intended to go in a worksheet code module, not the ThisWorkbook code module. Move the code to the sheet 2 and/or sheet 3 code module and try again. I would recommend starting with just sheet 2 and get things working there first, then we can talk about what workbook-level event code in ThisWorkbook might work for you.

 

[6StringJazzer]

3. I added the VBA code under "thisworkbook".

Sorry, I should have been more specific. The code goes into the module for the sheet where the user is entering the data.

 

[fmorenojr]

Sorry, I should have been more specific. The code goes into the module for the sheet where the user is entering the data.

I added the code on one of the sheets and it's still not working. I'm wondering if it has anything to do with the below. the two sheets where the data is supposed to go has a custom name, so does the other sheet (3rd image). LOC is Location.

 

[rlv01]

      If Cell.Value <> "" And Cell.Column = 1 Then ' column A

If you have not done it already, change Cell.Column = 1 to Cell.Column = 4 so it references col D instead of col A

 

[fmorenojr]

      If Cell.Value <> "" And Cell.Column = 1 Then ' column A

If you have not done it already, change Cell.Column = 1 to Cell.Column = 4 so it references col D instead of col A

I tried that and I get this error message.

 

[6StringJazzer]

It appears that you did not format column D as Text.