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