CODEWITHSUNDEEP
vbaexcel
user6537067
user6537067

Reputation: 397

Replacing numbers based on length and first 3 charcters

I'm trying to replaced numbers on a range that contains mobile numbers of clients. I would like to change the numbers that are not 6 digit ,7 digit ,10 digit, and 11 digit numbers also I also would like to change 10 digit numbers whose first 3 digits are not "032" . Here is what I have come up so far:

Sub changetold()
    Dim rCel As Range
    Dim sTxt As String
    Dim first3numbers As String
    Dim lastrow, i, currentcell As Long

    Const MaxLength As Long = 11
    Const MinLength As Long = 6
    Const MidLength As Long = 7
    Const MaxmidLength As Long = 10

    first3numbers = "032"

    With ActiveSheet
        lastrow = .Cells(.Rows.count, "AC").End(xlUp).row
    End With

    currentcell = 12

    For i = 13 To lastrow
        currentcell = currentcell + 1
        sTxt = ActiveSheet.Range("CW" & currentcell).Value

        MsgBox (Len(sTxt))

        If Len(sTxt) <> MaxLength Or Len(sTxt) <> MinLength Or Len(sTxt) <> MidLength Or Len(sTxt) <> MaxmidLength Then
            sTxt = "101011"
        End If

        If Left(sTxt, 3) <> "032" And Len(sTxt) = MaxmidLength Then
            sTxt = "101011"
        End If
    Next i
End Sub

The code does change the cells but the problem is it is inaccurate. I would like to know how this will be done correctly like the sample image below:

Initial data

Desired result

Upvotes: 1

Views: 87

Answers (2)

Dy.Lee
Dy.Lee

Reputation: 7567

I think the code would be like this

Sub changetold()
    Dim sTxt As String
    Dim lastrow As Long, i As Long

    With ActiveSheet
        lastrow = .Cells(.Rows.Count, "AC").End(xlUp).Row
    End With

    For i = 13 To lastrow
        sTxt = Range("CW" & i)

        Select Case Len(sTxt)
        Case 6, 7, 11
        Case 10
            If Left(sTxt, 3) <> "032" Then Range("cw" & i) = "101011"
        Case Else
            Range("cw" & i) = "101011"
        End Select
    Next i
End Sub

Upvotes: 3

Siddharth Rout
Siddharth Rout

Reputation: 149325

sTxt = ActiveSheet.Range("CW" & currentcell).Value

Nothing is happening because you are storing the value in a variable, making changes to it but never writing it back to the cell.

Also instead of using so many Ifs, use Select Case. It will make your life easier :)

Is this what you are trying? (untested)

Sub changetold()
    Dim lastrow As Long, i As Long

    With ActiveSheet
        lastrow = .Cells(.Rows.Count, "AC").End(xlUp).Row

        For i = 13 To lastrow
            Select Case Len(.Range("CW" & i).Value)
                Case 6, 7, 11
                Case 10: If Left(.Range("CW" & i).Value, 3) <> "032" Then .Range("CW" & i).Value = 101011
                Case Else: .Range("CW" & i).Value = 101011
            End Select
        Next i
    End With
End Sub

Upvotes: 2

Related Questions