View Single Post
  #7   Report Post  
Posted to microsoft.public.excel.programming
Tom Ogilvy Tom Ogilvy is offline
external usenet poster
 
Posts: 27,285
Default Compare and update arrays

A1:B3 held your Array2 values
C1:D3 held your Array1 values

Added 3 blank cells to the range for Array2 so it had space for additions.

Sub Wash()

iCount = 1
iRow = 1
Array1 = Range("C1:D3").Value
Array2 = Range("A1:B6").Value
Do While iRow <= UBound(Array1, 1)
'if elements in both arrays are equal then move on...
bAdded = False
Do While Trim(Array1(iRow, 1)) < Trim(Array2(iCount, 1))
'but if not, then check if array2 is empty
If IsEmpty(Array2(iCount, 1)) = True Then
'if empty, then add the new element to array2
Array2(iCount, 1) = Trim(Array1(iRow, 1))
Array2(iCount, 2) = Trim(Array1(iRow, 2))
bAdded = True
Exit Do
End If
'Otherwise, if the array is not empty, then increment the
' counter in array2
iCount = iCount + 1
' Loop back, checking each of the elements in array2 for a match
' with array1. If no match is found then the value
' is added to array2 as above
Loop
' Now if the compared elements match, then assign
' the other values to array2
If Not bAdded Then
If Trim(Array2(iCount, 1)) = Trim(Array1(iRow, 1)) Then
Array2(iCount, 2) = Trim(Array1(iRow, 2))
End If
End If
'Now that the values have been assigned, move on to
' the next element in array1
iRow = iRow + 1
iCount = 1
Loop
Range("A10:B15").Value = Array2
End Sub

this produced the results you show.
--
Regards,
Tom Ogilvy


"DAO" wrote in message
ups.com...
Hi Tom,

Yea I guess I declared it incorrectly. They should be 2 dimensional
arrays (3x2). Elements (1,1) = "a", (1,2) = 1, (2,1) = "b" etc