CODEWITHSUNDEEP
vbaexcel
efrizal nugros
efrizal nugros

Reputation: 1

Excel-vba How to make macro replace 5 last character and add it into new column until last row

I am trying to Add an Value from B2 and then change 5 last digit to "*" and put it in column c2 until last row. But I always get an error.

    B                    C
 3321072204140001   332107220414*****

I'm trying this

Sub Macro3()
 Sheets("Sheet1").Select
     Range(Range("O2"), Range("O" & Rows.Count).End(xlUp)).Select
Selection.Formula = "=REPLACE(B2,13,4,"*****")"
    Selection.Columns.AutoFit    
End Sub

Can you please let me know the correct code? Thanks :)

Upvotes: 0

Views: 563

Answers (3)

chris neilsen
chris neilsen

Reputation: 53136

Three issues in your code

  1. Missing escaping of " 's
  2. Your math is wrong. (Code based on replacing Last 5 characters as per Q title)
  3. Unnecessary use of Select
Sub Macro3()
    Dim ws As Worksheet
    Set ws = ActiveWorkbook.Worksheets("Sheet2")
    With ws
        .Range(.Range("O2"), .Range("O" & .Rows.Count).End(xlUp)).Formula = _
          "=REPLACE(B2,LEN(B2)-4,5,""*****"")"
        .Columns.AutoFit
    End With
End Sub

Upvotes: 1

Dan Donoghue
Dan Donoghue

Reputation: 6216

Does it have to be with code?

You can use a formula =left(B2,len(B2)-5)&"*****"

alternatively you can do it through code then copy and paste the values:

Range("C2:C" & Range("B" & Rows.Count).End(xlUp).Row).Formula = "=left(B2,len(B2)-5) & ""*****"""
Range("C2:C" & Range("C" & Rows.Count).End(xlUp).Row).Copy
Range("C2:C" & Range("C" & Rows.Count).End(xlUp).Row).PasteSpecial xlPasteValues

Upvotes: 0

urdearboy
urdearboy

Reputation: 14590

You can try this, where the string to be modified is in Col B and the modified string is in Col C (values will be side by side).
I'm using Col B to determine last row here, although it looks like you were using Col O. Not sure if that is a typo or intentional. Either way, update that line as needed.

Sub Macro3()

Dim WS As Worksheet
Set WS = ThisWorkbook.Sheets("Sheet1")

Dim LRow As Long, i as Long, MyString As String, NewString As String, StrLen as Integer
LRow = WS.Range("B" & WS.Rows.Count).End(xlUp).Row

For i = 2 To LRow                                           'Loop through all B strings (header excluded)
    MyString = WS.Range("B" & i)                               'Variable just to make steps easier to read
    StrLen = Len(MyString) - 5                             'Resize String
    NewString = Trim(Left(MyString, StrLen)) & "*****"     'Remove unwanted spaces, remove last 5, add new 5
    WS.Range("C" & i) = NewString                           'Assign to your range
Next i

WS.Range("B:C").EntireColumn.AutoFit

End Sub

Upvotes: 0

Related Questions