Matching elements of two character arrays

[volabos]

Good morning,

Suppose I have an reference array with following elements :

RefArray = "A", "B", "C", "D"

Now user has supplied following array :

UserArray = "C", "D", "A"

I need to write some VBA code which will return the position of the element in UserArray w.r.t. the RefArray. For present case, I need to find another array of integer like

res = 3, 4, 1

As, "C" in the UserArray corresponds to the 3rd element of RefArray and so on.

Any suggestion will be highly appreciated.

Best,

[stanleydgrom]

volabos,

Try:




Option Explicit
Option Base 1
Sub Get_res()
' stanleydgrom, 02/13/2010
Dim RefArray, UserArray, res
Dim a As Long, b As Long, Answer As String
RefArray = Array("A", "B", "C", "D")
UserArray = Array("C", "D", "A")
ReDim res(1 To UBound(UserArray))
Answer = ""
For a = LBound(UserArray) To UBound(UserArray)
For b = LBound(RefArray) To UBound(RefArray)
If RefArray(b) = UserArray(a) Then
res(a) = b

'If you step thru the code,
' this will show the values in the res Array
Debug.Print res(a)

Answer = Answer & b & ", "
Exit For
End If
Next b
Next a
If Right(Answer, 2) = ", " Then Answer = Left(Answer, Len(Answer) - 2)
MsgBox "Answer = " & Answer
End Sub




Have a great day,
Stan

[Bob Phillips]

An alternative way



Sub Array()
Dim RefArray As Variant
Dim UserArray As Variant
Dim ResultsArray As Variant
Dim ResultsIndex As Long
Dim i As Long

RefArray = Array("A", "B", "C", "D")
UserArray = Array("C", "D", "A")
ReDim ResultsArray(LBound(UserArray) To UBound(UserArray))

ResultsIndex = LBound(UserArray, 1)
For i = LBound(UserArray) To UBound(UserArray)

ResultsArray(ResultsIndex) = Application.Match(UserArray(i), RefArray, 0)
ResultsIndex = ResultsIndex + 1
Next i
End Sub

[Bob Phillips]

And another way



Public Sub GetArray()
Dim RefArray As Variant
Dim UserArray As Variant
Dim ResultsArray As Variant

RefArray = Array("A", "B", "C", "D")
UserArray = Array("C", "D", "A")
ResultsArray = Application.Index(UserArray, 0)
ResultsArray = Application.Evaluate("IF(ROW(1:1),MATCH(" & _
"{""" & Join(Application.Index(UserArray, 0), """,""") & """}" & "," & _
"{""" & Join(Application.Index(RefArray, 0), """,""") & """}" & ", 0))")
End Sub