Reputation: 1845
This is for an EXCEL Visual Basic Macro.
I need to change all column B data to only have alphanumeric characters on column C.
My code works only for one cell. I want to loop this code for every active cell. Can anybody help me on this?
Sub macroalphanum()
Dim a$, b$, c$, i As Integer
a$ = Range("C1").Value
For i = 1 To Len(a$)
b$ = Mid(a$, i, 1)
If b$ Like "[A-Z,a-z,0-9, ]" Then
c$ = c$ & b$
End If
Next i
Range("D1").Value = c$
End Sub
Upvotes: 0
Views: 754
Reputation: 11905
You just need to nest your For Loop in another For Loop that goes through each cell
Quick Example:
Sub macroalphanum()
Dim a$, b$, c$, i As Integer, r as Integer
For r=1 to 100 step 1 ' This will do it for all rows from 1 to 100. Change it to suit your needs
a$ = Cells(r,3).Value
c$ = "" ' (Re)Initialize c
For i = 1 To Len(a$)
b$ = Mid(a$, i, 1)
If b$ Like "[A-Z,a-z,0-9, ]" Then
c$ = c$ & b$
End If
Next i
Cells(r,4).Value = c$
Next r
End Sub
Upvotes: 2