PDA

View Full Version : ActiveCell.Value=replace........not putting range in new column.



ctallen23
04-30-2016, 04:52 PM
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
04-30-2016, 05:22 PM
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
05-04-2016, 12:15 PM
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