ActiveCell.Value=replace........not putting range in new column.

[ctallen23]

Hi,

I am trying to create a macro that will run as a loop, and remove wildcards (using Replace) from the strings. The new string will be filled in column B under new account. I've gotten the loop to run, and in this case, go from b2:b6, but it doesn't fill in a new string (minus the wildcards). So the 1st new number should be 5555....with 2 different wildcards removed.

I am trying to understand the Offset. but how it works is confusing. My initial attempt was to write Offset(, 1) so to put new number in column B.....BUT I don't know how to incorporate that.....SO the code I have below, is what I've worked with thus far.

The loop will run until it encounters a blank cell in A.

accountnumber newaccount
55#5#5%
889#94%84$
48$795#$%^
97#65489789&
87$&%980

Sub leftcolumn()
 
Dim accountnumber As String
Dim Newaccount As String
'Dim characters As Integer
'characters = InStr(InStr(InStr(InStr(InStr(InStr(accountnumber, "-"), "#"), "$"), "%"), "^"), "&")
Range("b2").Select
Newaccount = accountnumber
Do Until ActiveCell.Offset(0, -1).Value = ""
ActiveCell.Value = Replace(Replace(Replace(Replace(Replace(Replace(accountnumber, "-", ""), "#", ""), "$", ""), "%", ""), "^", ""), "&", "")
'ActiveCell = Str(accountnumber)
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Much appreciated!!

[Paul_Hossler]

1. Welcome

2. You can use code tags with the [#] icon to do the formatting

3. I left this a little wordy so your could follow my logic

Option Explicit

Sub LeftColumn()

    Dim LastRow As Long, RowNum As Long, WildCard As Long
    Dim AccountNumber As String
    Dim NewAccount As String
    Dim WildCardChars As String

    WildCardChars = "-#$%^&"

    Application.ScreenUpdating = False

    With ActiveSheet
        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        For RowNum = 2 To LastRow
            AccountNumber = .Cells(RowNum, 1).Value
            NewAccount = AccountNumber
 
            For WildCard = 1 To Len(WildCardChars)
                NewAccount = Replace(NewAccount, Mid(WildCardChars, WildCard, 1), vbNullString)
            Next WildCard
            
            .Cells(RowNum, 2).Value = NewAccount
        Next RowNum
    End With

    Application.ScreenUpdating = True

End Sub

[jolivanes]

Another way. It'll throw out everything but numbers from 0 (zero) to 9 (nine).
You don't have to put the different wildcards in.

Sub ctallen23()
    Dim c As Range, j As Long, x As String
    For Each c In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        For j = 1 To Len(c.Value)
            If Mid(c, j, 1) Like "[0-9]" Then x = x & Mid(c, j, 1)
            'or
            'If IsNumeric(Mid(c, j, 1)) Then x = x & Mid(c, j, 1)
        Next j
        c.Offset(, 1).Value = x
        x = ""
    Next c
End Sub