Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
help with EXCEL SCRIPT | Excel Discussion (Misc queries) | |||
help with EXCEL SCRIPT | Excel Discussion (Misc queries) | |||
Excel to SQL Script | Excel Programming | |||
I need a VB script for Excel | Excel Programming | |||
Excel 2000/XP script to Excel97 script | Excel Programming |