Compare Range list with String Array list (both same # of values)

[mgmAB]

Hi, here is my problem.

The following code shows two separate lists of strings stored as arrays (arrOne & arrTwo). I read both arrays into a 2 dimensional array (arrDestination) and print out the results;

Sub Test()

Dim i As Integer
Dim arrOne() As String, arrTwo() As String

arrOne = Split("John,James,Jack,Joe", ",")
arrTwo = Split("John,James,Fred,Joe", ",")

'assuming that each source has the same number of names
ReDim arrDestination(0 To 1, 0 To UBound(arrOne))

For i = 0 To UBound(arrDestination, 2)
arrDestination(0, i) = arrOne(i)
arrDestination(1, i) = arrTwo(i)
Debug.Print arrDestination(0, i), arrDestination(1, i)
Next

End Sub

Output:

John          John
James        James
Jack           Fred
Joe            Joe

This works, but what I'd really like to do is store a range of values from the worksheet into the second array (arrTwo). I've tried inserting the following code lines which stores 4 values from A1 - D1;

Dim myArray As Variant
myArray = Range("A1:D1").Value
arrTwo = Split(myArray, ",")

The last line is replaces the previous arrTwo array. I've tried other ways of storing the range into the array, but when it comes to running the macro I either get errors with how the array is stored, or errors which seem to suggest the 2D array can not read the values.

I would also like to do a comparison on the final 2D array and output a msg stating if all rows of the 2D array match or not. Appologies if this has been covered, I did have a good look through the forum.

Would appreciate any help
Regards

[AB33]

You can not split myArray = Range("A1:D1").Value as you do not have a separator. You loaded a range in to an array. If you want to access the elements within the array, you need to loop through the array. Each element has an index number.

For instance.

For each arrayindex in myArray
magbox=myArray (arrayindex)

Next