Macro or Function

[Daniell]

I have a spreadsheet that I have to change character in
the cell. I am not sure if there is a function, other
than the find and replace, or a macro that can be written
to do the job. There are four cells that depending on
what is in them is what to be changed to. These is a
little section of the spreadsheet and what it is to look
like:

A B C D E F
1234 A VR ST 15.00 Cutter
2345 A VP UP 10.00 Board
3456 A MG ST 05.00 Connector
4567 N DM ST 12.00 Line

A B C D E F
1234 Active Virtual Standard 15.00 Cutter
2345 Active Pinned Upright 10.00 Board
3456 Active Mult Standard 05.00 Connector
4567 NON Depend Standard 12.00 Line

The rows will always be the same depending on what is in
the cell for example "A" in colume "B" will always
be "ACTIVE" and "MG" in colume "C" will always be "MULT".
I am not sure if a "LOOKUP" will replace or if a macro can
be written to do each colume or not.

Thanks in advance for the help

 

[Bob Phillips]

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
Select Case True
.Column = 2
Select Case UCase(.Value)
Case "A": .Value = "Active"
Case "N": .Value = "NON"
End Select
.Column = 3
Select Case UCase(.Value)
Case "VR": .Value = "Virtual"
Case "VP": .Value = "Pinned"
Case "MG": .Value = "Mult"
Case "DM": .Value = "Depend"
End Select
.Column = 4
Select Case UCase(.Value)
Case "ST": .Value = "Standard"
Case "UP": .Value "Upright"
End Select
End Select
End With

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

 

[Bob Phillips]

Sorry, typos
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
Select Case True
Case .Column = 2
Select Case UCase(.Value)
Case "A": .Value = "Active"
Case "N": .Value = "NON"
End Select
Case .Column = 3
Select Case UCase(.Value)
Case "VR": .Value = "Virtual"
Case "VP": .Value = "Pinned"
Case "MG": .Value = "Mult"
Case "DM": .Value = "Depend"
End Select
Case .Column = 4
Select Case UCase(.Value)
Case "ST": .Value = "Standard"
Case "UP": .Value = "Upright"
End Select
End Select
End With

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

 

[Daniell]

I hate to say this but I cannot figure out how to activate
this code. I have looked at the Microsoft web site under
EVENT Code, Worksheet Event Code, but I stiil am not
sure. Any help would be appreciated.

 

[Bob Phillips]

It is activated by typing A or N in column B, VR, VP, MG or DM in column C
etc.

 

[Guest]

I have attempted that but it does not change.

-----Original Message-----
It is activated by typing A or N in column B, VR, VP, MG or DM in column C
etc.

--

HTH

RP




.

 

[Daniell]

I have attempted that but the characters do not change.

 

[Bob Phillips]

Sorry I thought I had fixed that

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
Select Case True
Case .Column = 2
Select Case UCase(.Value)
Case "A": .Value = "Active"
Case "N": .Value = "NON"
End Select
Case .Column = 3
Select Case UCase(.Value)
Case "VR": .Value = "Virtual"
Case "VP": .Value = "Pinned"
Case "MG": .Value = "Mult"
Case "DM": .Value = "Depend"
End Select
Case .Column = 4
Select Case UCase(.Value)
Case "ST": .Value = "Standard"
Case "UP": .Value = "Upright"
End Select
End Select
End With

ws_exit:
Application.EnableEvents = True
End Sub