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!!
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!!