0

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
Community
  • 1
  • 1
ingalcala
  • 1,785
  • 3
  • 14
  • 24
  • One should avoid declaring variables like `Dim a$, b$, c$`. You might want to see [THIS](http://stackoverflow.com/questions/10890892/use-of-symbol-hash-in-vba-macro/10891051#10891051) – Siddharth Rout Oct 22 '13 at 17:41
  • @SiddharthRout, that answer just explains what they symbols are. As someone who grew up with GWBASIC/MSBASIC, the use of $ is obvious to me. To newer users, the use of symbols is depreciated, so it can be confusing to see code that looks like `q#=c@/i%*x&` _(not that I've written code like that **whistles innocently**)_ – SeanC Oct 22 '13 at 18:10
  • @SeanCheshire: `that answer just explains what they symbols are.` No :) it does something beyond that :) – Siddharth Rout Oct 22 '13 at 19:18

1 Answers1

2

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
Julien Marrec
  • 11,605
  • 4
  • 46
  • 63
  • This loop is working only for the result, what I need is: A1 with Special characters to B1 only alphanumerics. The loop that you did on this code is doing A1 with special characters to B1:B100 alphanumeric same information. – ingalcala Oct 22 '13 at 16:57
  • It's not. Cells(r,3) takes row R, column 3. Then I give the output to Cells(r,4) which is row R, column 4. So it's C1 > D1 C2 > D2 C3 > D3 Etc. I haven't tested it but I don't see why it wouldn't work – Julien Marrec Oct 22 '13 at 17:11
  • it seems is doing this: A1 > B1 ; A1,A2 > B2 ; A1,A2,A3 > B3 ; A1,A2,A3,A4 > B4 ... and so on. – ingalcala Oct 22 '13 at 17:13
  • Ahah. Right. You need to empty c before looping again :) I'll correct the above code, sorry about that. I couldn't test the code (hate Excel on Mac) – Julien Marrec Oct 22 '13 at 17:44
  • I was confused, I was adding c+1 thinking this was the issue. Good thing you can figure this out fast. – ingalcala Oct 22 '13 at 17:53