0

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,

Community
  • 1
  • 1
Dara Viro
  • 11
  • 1
  • 4

3 Answers3

2

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.

L42
  • 19,427
  • 11
  • 44
  • 68
  • 1
    Since you are switching off events, you may want to see [THIS](http://stackoverflow.com/questions/13860894/ms-excel-crashes-when-vba-code-runs/13861640#13861640) – Siddharth Rout Feb 03 '14 at 06:15
  • 1
    @SiddharthRout oh, never thought of that. so i miss #3. That is good to know. :) – L42 Feb 03 '14 at 06:25
  • Editted my answer following @SiddharthRout advise. I also did some testing and it is true that if you encounter error, event is totally disabled. For example you delete range of cells. without the error handling routine and you came up with a `Type Mismatch`, the event fails and event is disabled. – L42 Feb 03 '14 at 06:33
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 :)

enter image description here

Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
0
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
SeekingAlpha
  • 7,489
  • 12
  • 35
  • 44
  • This assumes all your data is in the same column. It is a bit hard to determine what exactly you want to do based on what you just explained. If you want something to automatically change the value as you type into a cell then I have no idea how to make a macro that automatically knows where your cursor is and I would also argue if that is what you want just hard code the data... – SeekingAlpha Feb 03 '14 at 04:28