Auto get value in excel VBA

Question

I want to get automatic value in excel VBA in case I type AOM letter in cell, it will shows automatic data 1. if it is BOM letter, it will shows 2. if it is COM letter, it will shows 3.

In example, AOM=1,BOM=2,COM=3

How to write excel vba code to change automatic value in excel cell as condition above.

Regards,

Answer 1

NON VBA SOLUTION

As mentioned in the comment above, Why not a simple Excel Formula? If you want to update the same cell then why not a Data Validation List or say AutoCorrect?

Not sure if you are aware of the AutoCorrect Option in Excel. You can use the AutoCorrect feature to correct typos and misspelled words, as well as to insert symbols and other pieces of text. We will use this feature to do what you want... i.e replace AOM by 1, BOM by 2, COM by 3.

Do This

1. Click the File tab | Options.
2. In Excel Options dialog Box, Click on Proofing.
3. On the AutoCorrect tab, make sure the Replace text as you type check box is selected.
4. In the Replace box, type a AOM
5. In the With box, type 1
6. Click Add.
7. Repeat for BOM and COM
8. Click OK and you are done :)

Answer 2

Sub convert()
Range("A1").Select
While ActiveCell.Value <> ""
If ActiveCell.Value = "AOM" Then
ActiveCell.Value = 1
ElseIf ActiveCell.Value = "BOM" Then
ActiveCell.Value = 2
ElseIf ActiveCell.Value = "COM" Then
ActiveCell.Value = 3
Else
ActiveCell.Value = ActiveCell.Value + " Error!"
End If
ActiveCell.Offset(1, 0).Select
Wend
End Sub

Answer 3

This maybe?

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo errhandler
Application.EnableEvents = False
If Target.Value = "AOM" Then Target.Value = 1
If Target.Value = "BOM" Then Target.Value = 2
If Target.Value = "COM" Then Target.Value = 3
continue:
Application.EnableEvents = True

Exit Sub
errhandler:
MsgBox Err.Description
Resume continue

End Sub

Put code in any Sheet Object.
Hope this gets you started.