ExcelBanter - View Single Post

Alan Beban[_2_]

wrote:
I'm trying to create a function that can compare all the values in a
2-D array along the first parameter of the second dimention and then,
if there is a match, add together the elements in both arrays into a
third - except for the two elements in commen, with a complication that
the last element goes into the a different element in the merged array
--- see below

Function BuildArray(a1,a2)
Dim a3(1,1)
count = 0
For i = 1 to UBound(a1,1)
For j = 1 to UBound(a2,1)
If a2(j,1) = a1(i,1) Then
count = count+1
ResizeArray a3,vcount,2
a3(vcount,1) = a1(i,1) & a1(i,2) & a2(i,2) & a2(i,3)
a3(vcount,2) = a2(i,4)
End If
Next j
Next i
End Function

The complication is that I know that the first array passed (a1) will
always have a dimension of (n,2) but the second can vary (but will
always be at least 2)...

If a2 only has 2 dimensions then I'd want
a3(vcount,1) = a1(i,1) & a1(i,2)
a3(vcount,2) = a2(i,2)

If a2 had more than 2 dimensions then I'd want
a3(vcount,1) = a1(i,1) & a1(i,2) & a2(i,2) & a2(i,n-1)
a3(vcount,2) = a2(i,n)

I'm calling this quite often and right now I have it listed out in
specific detail over and over again (roughly 25 times)
and that tells me a function might be more useful and clear.

I have no idea what vcount is, but you might consider something like the
following:
On Error Resume Next

'Loop until an error occurs
i = 1
Do
z = UBound(a2, i)
i = i + 1
Loop While Err = 0

'Reset the error value for use with other procedures
Err = 0

'Return the number of dimensions
k = i - 2

If k=2 then
a3(vcount,1) = a1(i,1) & a1(i,2)
a3(vcount,2) = a2(i,2)
Else
a3(vcount,1) = a1(i,1) & a1(i,2) & a2(i,2) & a2(i,k-1)
a3(vcount,2) = a2(i,k)
EndIf

Or, if you regularly have the array functions from my web site available
to your workbook, you could use k = ArrayDimensions(a2) instead of the loop.

Alan Beban