Excel help with VB Script
 

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

Excel help with VB Script
 

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


"Kevin Lisboa" wrote in message
...
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

Excel help with VB Script
 

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

"Kevin Lisboa" wrote:

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

Excel help with VB Script
 

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

"JLGWhiz" wrote:

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


"Kevin Lisboa" wrote in message
...
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

Excel help with VB Script
 

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.


"JLatham" wrote in message
...
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??

"JLGWhiz" wrote:

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


"Kevin Lisboa" wrote in message
...
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

Excel help with VB Script
 

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.

"JLGWhiz" wrote:

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.


"JLatham" wrote in message
...
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??

"JLGWhiz" wrote:

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


"Kevin Lisboa" wrote in message
...
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

Excel help with VB Script
 

This appears to have worked spot on!

Thank you so much for the help on this!

--Kevin

"FSt1" wrote:

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

"Kevin Lisboa" wrote:

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

Excel help with VB Script
 

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

"JLGWhiz" wrote:

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


"Kevin Lisboa" wrote in message
...
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