If I use 10 macro is ok but if I use 10: Auckland City Depot it is
 

Hello from Steved

Case "10" is fine
Case "10: Auckland City Depot" is this possible please.
The complete macro is below. I Thankyou.

10: Auckland City Depot, Change to "City"
11: Panmure Depot, Change to "Panmure"
20: Swanson Depot, Change to "Go West"
30: Roskill Depot, Change to "Roskill"
40: Wiri Depot, Change to "Wiri"
50: Shore Depot, Change to "Northstar Shore"
51: Orewa Depot, Change to "Northstar Orewa"

Sub NumberToDepot()
Dim x As Long
For x = 1 To Range("a" & Rows.Count).End(xlUp).Row Step 20
Select Case Left(Cells(x, 1).Value, 20)
Case "10: Auckland City Depot"
Cells(x, 1).Value = "City"
Case "30: Roskill Depot"
Cells(x, 1).Value = "Roskill"
Case "40: Wiri Depot"
Cells(x, 1).Value = "Wiri"
Case "50: Shore Depot"
Cells(x, 1).Value = "Northstar Shore"
Case "51: Orewa Depot"
Cells(x, 1).Value = "Northstar Orewa"
Case "20: Swanson Depot"
Cells(x, 1).Value = "Go West"
Case "11: Panmure Depot"
Cells(x, 1).Value = "Panmure"
End Select
Next x
End Sub

If I use 10 macro is ok but if I use 10: Auckland City Depot it is
 

Hi Steved,

Do I understand your problem to be that you are not getting a match for
Case "10: Auckland City Depot"? If so then it is because that string is 23
characters long and your case asks for 20 characters so therefore the strings
will not match.

--
Regards,

OssieMac


"Steved" wrote:

Hello from Steved

Case "10" is fine
Case "10: Auckland City Depot" is this possible please.
The complete macro is below. I Thankyou.

10: Auckland City Depot, Change to "City"
11: Panmure Depot, Change to "Panmure"
20: Swanson Depot, Change to "Go West"
30: Roskill Depot, Change to "Roskill"
40: Wiri Depot, Change to "Wiri"
50: Shore Depot, Change to "Northstar Shore"
51: Orewa Depot, Change to "Northstar Orewa"

Sub NumberToDepot()
Dim x As Long
For x = 1 To Range("a" & Rows.Count).End(xlUp).Row Step 20
Select Case Left(Cells(x, 1).Value, 20)
Case "10: Auckland City Depot"
Cells(x, 1).Value = "City"
Case "30: Roskill Depot"
Cells(x, 1).Value = "Roskill"
Case "40: Wiri Depot"
Cells(x, 1).Value = "Wiri"
Case "50: Shore Depot"
Cells(x, 1).Value = "Northstar Shore"
Case "51: Orewa Depot"
Cells(x, 1).Value = "Northstar Orewa"
Case "20: Swanson Depot"
Cells(x, 1).Value = "Go West"
Case "11: Panmure Depot"
Cells(x, 1).Value = "Panmure"
End Select
Next x
End Sub

If I use 10 macro is ok but if I use 10: Auckland City Depot i
 

Hi again,

I should have included insert your case like this and you will get a match.

Case Left("10: Auckland City Depot", 20)

--
Regards,

OssieMac

If I use 10 macro is ok but if I use 10: Auckland City Depot i
 

Hello OssieMac from Steved

Thankyou very much.


"OssieMac" wrote:

Hi again,

I should have included insert your case like this and you will get a match.

Case Left("10: Auckland City Depot", 20)

--
Regards,

OssieMac