I'm trying to take in a batch name typed by someone and run it through a loop to replace and illegal characters (!£$%...etc) with "_". Here's what I have:
'define illegal chars
illegalChars = "!,£,$,%,^,&,*"
'split illegal chars into an array
illegalCharsArr = Split(illegalChars, ",")
'take entered data from textbox
batch_name = batchEntryBox.Text
'split entered data into an array
batchNameArr = Split(batch_name, " ")
For Each item In batchNameArr
For Each i In illegalCharsArr
If batchNameArr(item) = illegalCharsArr(i) Then
batchNameEdit = batchNameArr.Replace(batchNameArr(item), illegalCharsArr(i))
End If
Next
Next
'post new batch name in Label box
Label1.Caption = batchNameEdit
End Sub
I can't get it to work. Also, how do i split a name that has no spaces?
Thanks!
CodePudding user response:
Try this logic.
Sub CleanTheWord()
illegalChars = "!,£,$,%,^,&,*"
Dim illegalCharsArr() As String
illegalCharsArr = Split(illegalChars, ",")
Dim batch_name As String
batch_name = "£sadg$u^su&bc!ksj*" 'Changed this for testing. Modify accordingly
Dim newstring As String
newstring = batch_name
Dim pos As String
For j = 1 to Len(batch_name)
pos = Mid(batch_name, j, 1)
For Each i In illegalCharsArr
If StrComp(pos, i, vbTextCompare) = 0 Then
newstring = VBA.Replace(newstring, pos, "_", 1, 1, vbTextCompare)
End If
Next
Next
MsgBox newstring, vbInformation
End Sub
Tried & Tested: