Storing Array to Find Replace Value via 2 Dimensional Array

Hi All,

Hopefully you can help.

I have an Array

Array1 = Array(a,b,c,d)
Array2 = Array(1,2,3,4)

the position between the two arrays relate ie a = 1, b = 2 and so on.

For several Macros i need to find all the values in column A which contain Array1 and replace them to the equivalent within array2. As this action is used within multiple Macors and the Large Array occasional updates i would like to store the Array itself as a Public Function to call upon ie.

Formula:

Public Function Replace(Array As String)



Select Case Array



Case Is = "ArrayOne"

    Replace = Array("a", "b", "c", "d")



Case Is = "ArrayTwo"



    Replace = Array("1", "2", "3", "4")



End Select



End Function

This would call within the Macro as follows

Formula:

For Each t In Replace("ArrayOne")

Columns("A").Replace What:=t, Replacement:= (Array 2 But How), LookAt:=xlPart, _

        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

        ReplaceFormat:=False

Next t

Any help which can be provided would be great!

Cheers

Thanks, the above works however only when all within one Macro.

Ideally i require the below as a public function to call upon

Formula:

Dim Array1, Array2, i As Long

Array1 = Array("a", "b", "c", "d")

Array2 = Array("1", "2", "3", "4")

Therefore the below would be within each of my individual Macros

Formula:

Call Public Function



For i = LBound(Array1) To UBound(Array1)

    Columns("A:A").Replace What:=Array1(i), Replacement:=Array2(i), LookAt:=xlPart, _

    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

    ReplaceFormat:=False

Next

Is this achievable at all?

Cheers

Works Perfect! Thank you very much