PDA

View Full Version : alphanumerics only



niceguy21
10-03-2012, 02:49 AM
Hello VBA friends :)

I am looking for a way of automating the removal of special characters in a range A1:A1000 that we are uploading bits of data into. Basically, the string "Łand$ mess~" entered into A1 would automatically become: "andmess" by removing those non-aphanumerics and replacing them with "".

I found some of this code online but I don't understand some of the lines.




Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
If Not Intersect(Target, Range("A1:A1000")) Is Nothing Then
Dim Target As Range
With CreateObject("vbscript.regexp")
.Global = True
.Pattern = "\W"
For Each Target In Selection.Cells
Target.Value = Replace(.Replace(Target.Value, ""), "_", "")
Next Target
End With
End Sub


Is there a standard way of doing this?

Best Regards :)

N:doh:

niceguy21
10-03-2012, 03:37 AM
I have built a working solution regarding this problem but the computer keeps crashing ?? this is built around the fact that the code works for module2 but this specifies a manual selection of cells, so I have built a auto macro that detects any changes and then calls another macro that chooses my specified range A1:A1000

in Sheet2:


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A1000")) Is Nothing Then Macro3
End Sub

in Module2

Sub Remove_Characters()
Dim c As Range
With CreateObject("vbscript.regexp")
.Global = True
.Pattern = "\W"
For Each c In Selection.Cells
c.Value = Replace(.Replace(c.Value, ""), "_", "")
Next c
End With
Range("A1").Select
End Sub



Module3

Sub Macro3()
Range("A1:A1000").Select
Application.Run "Book7.xlsm!Remove_Characters"
End Sub

snb
10-03-2012, 03:50 AM
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.column=1 Then
application.enableevents=false
target=replace(replace(target.value,"$",""),"~","")
application.enableevents=true
end if
End Sub

niceguy21
10-03-2012, 04:04 AM
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.column=1 Then
application.enableevents=false
target=replace(replace(target.value("$",""),"~","")
application.enableevents=true
end if
End Sub


Upon pasting this into sheet 2 object box,

This line is flagged red:

target=replace(replace(target.value("$",""),"~","")

I see there is a missing bracket ).. but even when adding this bracket I don't get anything happening?

Thanks

snb
10-03-2012, 04:17 AM
I amended the previous code.

Please check the VBEditor helpfiles to find out where to place this code.

PS. do not use code you do not fully understand.

niceguy21
10-03-2012, 04:26 AM
I amended the previous code.

Please check the VBEditor helpfiles to find out where to place this code.

PS. do not use code you do not fully understand.

Many thanks! But not having much luck with this.. :banghead:

snb
10-03-2012, 04:57 AM
And please do not quote every (whole) post.....

niceguy21
10-03-2012, 05:05 AM
understood.

Is there an obvious error in this code?


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A1000")) Is Nothing Then Remove_Characters
End Sub


//which refers to:



Sub Remove_Characters()
Dim c As Range
With CreateObject("vbscript.regexp")
.Global = True
.Pattern = "\W"
For Each c In Cells.Range("A1:A1000")
c.Value = Replace(.Replace(c.Value, ""), "_", "")
Next c
End With
Range("A1").Select
End Sub


?