Excel help with VB Script

[Kevin Lisboa]

Don't know if anyone out there uses vb scripting with Excel, but I'm new to
it, and need some guidance.

I've managed to find a script that will strip all the non alpha-numeric
values from a cell and spit out just the alphanumeric values.

My dillemma is that the script only works for one cell, and I need to apply
it to about 25,000 cells. Anyone know how to retool this code so it works
properly?

Sub test()

Dim a$, b$, c$, i As Integer

a$ = Range("F2").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("G2").Value = c$

End Sub

Basically, once it processes cell F2 and puts the results in G2, I want it
to move to F3 and place the data in G3, etc and so on...

Any ideas?

--McBean

 

[JLGWhiz]

This is untested so let me know if it errors out.

Assuming your 2500 cells are all in column F.

Sub test()
Dim a$ As Integer, b$ As Integer, c$ As Integer
Dim i As Integer, lr As Long, rng As Range

lr = ActiveSheet.Cells(Rows.Count, 6).End(slUp).Row
Set rng = ActiveSheet.Range("F2:F" & lr)

For Each r In rng
If Not r Is Nothing Then
a$ = r.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
r.Offset(0, 1).Value = c$
End If
Next c

End Sub

 

[FSt1]

hi
try this....
Sub test()

Dim a$, b$, c$, i As Integer
Dim lr As Long
Dim r As Range
lr = Cells(Rows.Count, "F").End(xlUp).Row 'find Last row

Set r = Range("F2:F" & lr) 'define working range
For Each cell In r

a$ = cell.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
cell.Offset(0, 1).Value = c$ 'set value
c$ = "" 'reset c$ to nothing
Next cell
End Sub

regards
FSt1

 

[JLatham]

Oddity question - nothing to do with the subject at hand. I just got notice
of change in this discussion, your posting no doubt. I see that it
supposedly has 3 posts (OPs + 2 more), but I only see 2, yours and the OPs,
and don't see my VBA solution that I posted at all? Are you seeing my other
post with VBA code, very similar to your modified VBS code??

This is untested so let me know if it errors out.

Assuming your 2500 cells are all in column F.

Sub test()
Dim a$ As Integer, b$ As Integer, c$ As Integer
Dim i As Integer, lr As Long, rng As Range

lr = ActiveSheet.Cells(Rows.Count, 6).End(slUp).Row
Set rng = ActiveSheet.Range("F2:F" & lr)

For Each r In rng
If Not r Is Nothing Then
a$ = r.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
r.Offset(0, 1).Value = c$
End If
Next c

End Sub

Don't know if anyone out there uses vb scripting with Excel, but I'm new
to
it, and need some guidance.

I've managed to find a script that will strip all the non alpha-numeric
values from a cell and spit out just the alphanumeric values.

My dillemma is that the script only works for one cell, and I need to
apply
it to about 25,000 cells. Anyone know how to retool this code so it works
properly?

Sub test()

Dim a$, b$, c$, i As Integer

a$ = Range("F2").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("G2").Value = c$

End Sub

Basically, once it processes cell F2 and puts the results in G2, I want it
to move to F3 and place the data in G3, etc and so on...

Any ideas?

--McBean

 

[JLGWhiz]

Hi Jim, I am working through OE right now, but I do not see the other
posting. The google news reader has been screwed up for months. That is
why I started using OE most of the time.

Oddity question - nothing to do with the subject at hand. I just got
notice
of change in this discussion, your posting no doubt. I see that it
supposedly has 3 posts (OPs + 2 more), but I only see 2, yours and the
OPs,
and don't see my VBA solution that I posted at all? Are you seeing my
other
post with VBA code, very similar to your modified VBS code??

This is untested so let me know if it errors out.

Assuming your 2500 cells are all in column F.

Sub test()
Dim a$ As Integer, b$ As Integer, c$ As Integer
Dim i As Integer, lr As Long, rng As Range

lr = ActiveSheet.Cells(Rows.Count, 6).End(slUp).Row
Set rng = ActiveSheet.Range("F2:F" & lr)

For Each r In rng
If Not r Is Nothing Then
a$ = r.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
r.Offset(0, 1).Value = c$
End If
Next c

End Sub

Don't know if anyone out there uses vb scripting with Excel, but I'm
new
to
it, and need some guidance.

I've managed to find a script that will strip all the non alpha-numeric
values from a cell and spit out just the alphanumeric values.

My dillemma is that the script only works for one cell, and I need to
apply
it to about 25,000 cells. Anyone know how to retool this code so it
works
properly?

Sub test()

Dim a$, b$, c$, i As Integer

a$ = Range("F2").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("G2").Value = c$

End Sub

Basically, once it processes cell F2 and puts the results in G2, I want
it
to move to F3 and place the data in G3, etc and so on...

Any ideas?

--McBean

 

[JLatham]

Thanks. I still don't see the other one - as you noted, this board is
screwed up in more ways than I can count at times.

Hi Jim, I am working through OE right now, but I do not see the other
posting. The google news reader has been screwed up for months. That is
why I started using OE most of the time.

Oddity question - nothing to do with the subject at hand. I just got
notice
of change in this discussion, your posting no doubt. I see that it
supposedly has 3 posts (OPs + 2 more), but I only see 2, yours and the
OPs,
and don't see my VBA solution that I posted at all? Are you seeing my
other
post with VBA code, very similar to your modified VBS code??

This is untested so let me know if it errors out.

Assuming your 2500 cells are all in column F.

Sub test()
Dim a$ As Integer, b$ As Integer, c$ As Integer
Dim i As Integer, lr As Long, rng As Range

lr = ActiveSheet.Cells(Rows.Count, 6).End(slUp).Row
Set rng = ActiveSheet.Range("F2:F" & lr)

For Each r In rng
If Not r Is Nothing Then
a$ = r.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
r.Offset(0, 1).Value = c$
End If
Next c

End Sub


Don't know if anyone out there uses vb scripting with Excel, but I'm
new
to
it, and need some guidance.

I've managed to find a script that will strip all the non alpha-numeric
values from a cell and spit out just the alphanumeric values.

My dillemma is that the script only works for one cell, and I need to
apply
it to about 25,000 cells. Anyone know how to retool this code so it
works
properly?

Sub test()

Dim a$, b$, c$, i As Integer

a$ = Range("F2").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("G2").Value = c$

End Sub

Basically, once it processes cell F2 and puts the results in G2, I want
it
to move to F3 and place the data in G3, etc and so on...

Any ideas?

--McBean

 

[Kevin Lisboa]

This appears to have worked spot on!

Thank you so much for the help on this!

--Kevin

hi
try this....
Sub test()

Dim a$, b$, c$, i As Integer
Dim lr As Long
Dim r As Range
lr = Cells(Rows.Count, "F").End(xlUp).Row 'find Last row

Set r = Range("F2:F" & lr) 'define working range
For Each cell In r

a$ = cell.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
cell.Offset(0, 1).Value = c$ 'set value
c$ = "" 'reset c$ to nothing
Next cell
End Sub

regards
FSt1

Don't know if anyone out there uses vb scripting with Excel, but I'm new to
it, and need some guidance.

I've managed to find a script that will strip all the non alpha-numeric
values from a cell and spit out just the alphanumeric values.

My dillemma is that the script only works for one cell, and I need to apply
it to about 25,000 cells. Anyone know how to retool this code so it works
properly?

Sub test()

Dim a$, b$, c$, i As Integer

a$ = Range("F2").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("G2").Value = c$

End Sub

Basically, once it processes cell F2 and puts the results in G2, I want it
to move to F3 and place the data in G3, etc and so on...

Any ideas?

--McBean

 

[Kevin Lisboa]

As you noted, this wasn't tested and it does error out during the first line
after Sub test:

"Dim a$ As Integer, b$ As Integer, c$ As Integer"

This line is all red in the vb utility with Excel.

--Kevin

This is untested so let me know if it errors out.

Assuming your 2500 cells are all in column F.

Sub test()
Dim a$ As Integer, b$ As Integer, c$ As Integer
Dim i As Integer, lr As Long, rng As Range

lr = ActiveSheet.Cells(Rows.Count, 6).End(slUp).Row
Set rng = ActiveSheet.Range("F2:F" & lr)

For Each r In rng
If Not r Is Nothing Then
a$ = r.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
r.Offset(0, 1).Value = c$
End If
Next c

End Sub

Don't know if anyone out there uses vb scripting with Excel, but I'm new
to
it, and need some guidance.

I've managed to find a script that will strip all the non alpha-numeric
values from a cell and spit out just the alphanumeric values.

My dillemma is that the script only works for one cell, and I need to
apply
it to about 25,000 cells. Anyone know how to retool this code so it works
properly?

Sub test()

Dim a$, b$, c$, i As Integer

a$ = Range("F2").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("G2").Value = c$

End Sub

Basically, once it processes cell F2 and puts the results in G2, I want it
to move to F3 and place the data in G3, etc and so on...

Any ideas?

--McBean