Find and replace text

[austin350s10]

I am trying to use the following script to search through a cells text and replace any invalid characters with "_". This script will identify the invalid characters but it will not replace the text. What am I doing wrong here?

Sub test()
Dim i As Integer
Dim Pos As Integer
Dim SearchString As String
Dim correction As String
 
SearchString = Range("A1").Value

FindChar = Array(":", "/", " ")

For Each c In FindChar
For i = 1 To Len(SearchString)
    If Mid(SearchString, i, 1) = c Then
        Pos = i
        correction = Replace(SearchString, i, "_")
        Range("A1").Value = correction
    End If
Next i
If Pos <> 0 Then
MsgBox Chr(34) & c & Chr(34) & " was Found at position " & Pos
End If
Pos = 0
Next c
End Sub

 

[poolhall]

in the Replace function the 2nd argument should be a string to find, not its position.

 

[poolhall]

Sub test2()
Range("A1").Value = Replace(Replace(Replace(Range("A1").Value, ":", "_"), "/", "_"), " ", "_")
End Sub

 

[austin350s10]

Is there a way to do it while still using the array of invalid characters because I will eventually need to remove 10 or so invalid characters and report back to the user which invalid characters they entered.

 

[poolhall]

Is there a way to do it while still using the array of invalid characters because I will eventually need to remove 10 or so invalid characters and report back to the user which invalid characters they entered.

try this:

Sub test()
Dim i As Integer
Dim SearchString As String
 
SearchString = Range("A1").Value

FindChar = Array(":", "/", " ")

For Each c In FindChar
    For i = 1 To Len(SearchString)
        If Mid(SearchString, i, 1) = c Then
            Range("A1").Value = Replace(SearchString, c, "_")
            MsgBox Chr(34) & c & Chr(34) & " was Found at position " & i
            SearchString = Range("A1").Value
            Exit For
        End If
    Next i
Next c
End Sub

 

[austin350s10]

Tweaked it just a bit. Works great thanks!!

Sub test()
Dim i As Integer
Dim Pos As Integer
Dim SearchString As String
Dim problems As String

SearchString = Range("A1").Value

FindChar = Array(" ", Chr(34), "/", "\", ":", "*", "?", "<", ">", "|")

For Each c In FindChar
    For i = 1 To Len(SearchString)
        If Mid(SearchString, i, 1) = c Then
            Pos = i
            Range("A1").Value = Replace(Range("A1").Value, c, "")
            problems = problems & vbNewLine & Chr(34) & c & Chr(34) & " was Found at position " & Pos
        End If
    Next i
Next c

If problems <> "" Then
    MsgBox "Invalid characters were found and removed:" & problems
End If

End Sub

 

[poolhall]

looks like you're going to get the original string as the result.

 

[austin350s10]

Naw, seems to be doing exactly what I want. Just needs to remove invalid and report back.