select case macro error debug

**arnab0711** · 01-29-2011, 01:47 PM

Hi,
I have this code which is not running the possible reason which can be is contents of column 'S' has two spaces after which I am not able to remove,pl advice

Sub replc()
    Dim i As Long
    For i = 2 To Range("S" & Rows.Count).End(xlUp).Row Step 1
        Select Case UCase(Range("S" & i).Value)
            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 Sub

**royUK** · 01-29-2011, 01:51 PM

This might help

Sub replc()
    Dim i As Long
    For i = 2 To Range("S" & Rows.Count).End(xlUp).Row Step 1
        Select Case UCase(Trim(Range("S" & i).Value))
            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 Sub

**arnab0711** · 01-29-2011, 02:18 PM

Hi,
I regret to say,the new code is not working,but I have attached the "master" spreadsheet which might help you in decision making.

**Alf** · 01-29-2011, 03:42 PM

Strange problem.

If I change your code:

Sub replcnetwork()
Dim i As Long
With Sheets("pivot")
    For i = 2 To Range("S" & Rows.Count).End(xlUp).Row Step 1

adding a range select comand:

Sub replcnetwork()
Dim i As Long
With Sheets("pivot")
Range("S2").Select
    For i = 2 To Range("S" & Rows.Count).End(xlUp).Row Step 1

you can loop through all the case statment so perhaps your code will work with this modification.

HTH

Alf

**Marcol** · 01-29-2011, 04:58 PM

@ 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

**pike** · 01-30-2011, 07:31 PM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save