@Josh,
Here is a sample using your test data. I've used Split and Join to do the substitution.
Code:
'---------------------------------------------------------------------------------------
' Procedure : testJosh
' Author : Jack
' Date : 27-Mar-18
' Purpose : Routine to convert/replace some ascii characters with others.
'---------------------------------------------------------------------------------------
' NOTES: The question is from https://www.accessforums.net/showthread.php?t=71318
'This routine uses the Arraychange to contain chars to change and
' arrayKeepIt to contin the replacements desired. The offsets/positions in these 2 arrays are sync'd
'when testing the sample data, thecode looks for the character to change and splits the test data.
'It then takes the replacement character and joins it with the remainder of the test data.
'In this sample the test string with the substituted character is printed to the immediate window.
'
' I used vbCompareBinary on the Split to ensure case sensitivity.
Sub testJosh()
Dim arrayChange As Variant, arrayKeepIt As Variant
10 On Error GoTo testJosh_Error
20 arrayChange = Array("Á", "á", "å", "ð", "Ð", "É", "é", "í", "Í", "Ó", "ó", "ú", "Ý", "ý")
30 arrayKeepIt = Array("A", "a", "a", "d", "D", "E", "e", "i", "I", "O", "o", "u", "Y", "y")
Dim i As Integer
Dim a(2) As String 'Test values
40 a(0) = "Árrow"
50 a(1) = "Ráw"
60 a(2) = "Håtch"
Dim strSplit() As String
Dim j As Integer
70 For j = LBound(a) To UBound(a)
80 If Nz(a(j), vbNullString) <> vbNullString Then
90 For i = 0 To UBound(arrayChange)
100 strSplit = Split(a(j), arrayChange(i), , vbBinaryCompare) 'check case equality
110 If UBound(strSplit) > 0 Then 'there maybe more chars to check
120 Debug.Print Join(strSplit, arrayKeepIt(i))
130 End If
140 Next i
150 End If
160 Next j
170 On Error GoTo 0
testJosh_Exit:
180 Exit Sub
testJosh_Error:
190 MsgBox "Error " & Err.Number & " on line " & Erl & " (" & Err.Description & ") in procedure testJosh of Module Module1"
200 GoTo testJosh_Exit
End Sub
Results of the test data:
Arrow
Raw
Hatch