@ Alf you get 6 of the strap for suggesting using select ...
@ arnab0711
There seems to be two problems that I can see
1/. You begin the code with a With Statement, then don't refer to it, you have "missed the point" so to speak!
2/. The Data in Column S is not e.g. IN it is actually IN & Char(160)&Char(160), I suspect this because the data is from the web or similar
If you check in the intermediate pane
? Len(UCase(.Range("S" & i)))
this will return 4, not 2 as expected.
Check
? Asc(Mid(UCase(.Range("S" & i)),3,1))
and
? Asc(Mid(UCase(.Range("S" & i)),4,1))
Both return 160
In this case the Left statement I have used solves the problem, (all cases are 2 characters long)
Better to clean the original data with a Substitute formula in your workbook
=SUBSTITUTE(S1,CHAR(160),"")
or maybe find and replace Char(160) with ""
Try this
Sub replcnetwork()
Dim i As Long
With Sheets("pivot")
For i = 1 To .Range("S" & Rows.Count).End(xlUp).Row Step 1
Select Case UCase(Left(.Range("S" & i), 2))
Case "BT"
.Range("T" & i).Value = "BTS"
Case "MW"
.Range("T" & i).Value = "Minilink / Access Link"
Case "BS"
.Range("T" & i).Value = "BSC"
Case "IN"
.Range("T" & i).Value = "IN"
Case "TE", "TS"
.Range("T" & i).Value = "Transmission"
Case "NM"
.Range("T" & i).Value = "OSS"
Case "SE"
.Range("T" & i).Value = "Services"
Case "AN"
.Range("T" & i).Value = "GSM antenna"
Case "NL", "OF"
.Range("T" & i).Value = "NLD-OFC"
Case "MP"
.Range("T" & i).Value = "MPBN"
Case "VA"
.Range("T" & i).Value = "VAS"
Case "TW", "ST", "DG", "TF", "IF", "PP", "BA", "AC", "IN", "SH", "PS", "UP", "BA", "AC"
.Range("T" & i).Value = "Civil & Infra"
Case "NW", "GP"
.Range("T" & i).Value = "VAS"
Case Else
' Don't change anything
End Select
Next i
End With
End Sub
Hope this helps
Bookmarks