Excel VBA Repeated Find and Replace /from a Table/

Question

I'm trying to build a macro in Excel using VBA – it should convert a slew of rows of strings like “YU-RN-PE-LT-JW-MN” into other things “L3,N6,W2,P9,V7,F2” (the dashes also ought to become commas) according to an arbitrary table specifying “YU” in one column, “L3” in another and so on.

It's the table thing that complicates normal search and find, and the repeated looking that makes normal Vlookup inadequade.

Would appreciate any help. Also please note that I've never used VBA before trying to build this macro, so assume I don't know anything.

Answer 1

Here is some sample code that does what you're trying to accomplish.

Sub multi_replace()
Dim inArr, subArr, temp As Variant
Dim outArr() As String
inArr = Sheets(1).UsedRange.Value 'Replace with correct range here and below
subArr = Sheets(2).UsedRange.Value
ReDim outArr(1 To UBound(inArr, 1), 1 To UBound(inArr, 2))
For x = 1 To UBound(inArr, 1)
    For y = 1 To UBound(inArr, 2)
        temp = Split(inArr(x, y), "-")
        For i = 0 To UBound(temp)
            For j = 1 To UBound(subArr, 1)
                If temp(i) = subArr(j, 1) Then
                    temp(i) = subArr(j, 2)
                    Exit For
                End If
            Next j
        Next i
        outArr(x, y) = Join(temp, ",")
    Next y
Next x
With Sheets.Add
    .Range("A1").Resize(UBound(outArr, 1), UBound(outArr, 2)).Value = outArr
    .Columns.AutoFit
End With
End Sub