Not changing from "City" to "1-City"

[Steved]

Hello from Steved

Below is not working I've got "City" in Col B is this my issue, if not
what is wrong please. example change "City" to "1-City"

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
With Target
Select Case LCase(Left(.Value, 4))
Case "City": .Value = "1-City"
Case "Rosk": .Value = "2-Rosk"
Case "Papa": .Value = "3-Papa"
Case "Wiri": .Value = "4-Wiri"
Case "Shor": .Value = "5-Shor"
Case "Orew": .Value = "6-Orew"
Case "Swan": .Value = "7-Swan"
Case "Panm": .Value = "8-Panm"
Case "Waih": .Value = "9-Waih"
End Select
End With

ws_exit:
Application.EnableEvents = True
End Sub

Thankyou.

 

[Norman Jones]

Hi Steved,

Try:

'==============>>
Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
With Target
Select Case LCase(Left(.Value, 4))
Case "city": .Value = "1-City"
Case "rosk": .Value = "2-Rosk"
Case "papa": .Value = "3-Papa"
Case "wiri": .Value = "4-Wiri"
Case "shor": .Value = "5-Shor"
Case "orew": .Value = "6-Orew"
Case "swan": .Value = "7-Swan"
Case "panm": .Value = "8-Panm"
Case "waih": .Value = "9-Waih"
End Select
End With

ws_exit:
Application.EnableEvents = True
End Sub

'<<==============

Note that all the Case tests have been changed to lower case.

 

[Steved]

Hello Norman from Steved

Thankyou for your reponse

The change will only occur if I highlite say "city" it then will change to
"1-City".

Is there something I've not done.

Cheers.

 

[Tom Ogilvy]

The event you chose fires when you edit a cell. As written, the code works
only on that cell. How do you want it to operate?

How do you intend to trigger the macro.

Since you posted your code in the change event and your case statement was
screwed up, Norman showed you how to correct the code you wrote. Thus the
assumption is that you wanted to use the change event to manipulate the data
you just entered.

 

[Steved]

Hello Tom from Steved

I copy from another file, so when I paste it this is when I wuold like the
macro to trigger form "City" to "1-City".

I think I've missed something but for the life off me I do not know what it
is.

Thankyou.

 

[Norman Jones]

Hi Steved,

I copy from another file, so when I paste it this is when I wuold
like the macro to trigger form "City" to "1-City".

With the modification to the Case statement, the code does this.

However, to allow for a multiple cell paste, try:

'==============>>
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCell As Range

Application.EnableEvents = False
On Error GoTo ws_exit

For Each rCell In Target.Cells
With rCell
Select Case LCase(Left(.Value, 4))
Case "city": .Value = "1-City"
Case "rosk": .Value = "2-Rosk"
Case "papa": .Value = "3-Papa"
Case "wiri": .Value = "4-Wiri"
Case "shor": .Value = "5-Shor"
Case "orew": .Value = "6-Orew"
Case "swan": .Value = "7-Swan"
Case "panm": .Value = "8-Panm"
Case "waih": .Value = "9-Waih"
End Select
End With
Next rCell

ws_exit:
Application.EnableEvents = True
End Sub
'<<==============--

 

[Steved]

Hello from Steved

That did it.

Thankyou verymuch.