RawlinsCross
Active Member
- Joined
- Sep 9, 2016
- Messages
- 437
When you transfer a single column continuous range (say 10 rows, single column) and transfer it to a range it gets stored as a 2-D array {vFirstArray of Type Variant/Variant(1 to 10, 1 to 1)
vFirstArray(1, 1)
vFirstArray(2, 1)
vFirstArray(3, 1)
vFirstArray(4, 1)
....
vFirstArrary(10, 1)
Say I have a second array of 2 elements that I want to combine: {vSecondArray of Type Variant/Variant(1 to 2, 1 to 1)
vSecondArray(1, 1)
vSecondArray(2, 1)
I can use a Function (see below) to Combine those two arrays - no problem, it works fine! However, if the second array is derived from a single cell the function breaks down as the second array is now {vSecondArray of Type Variant/Double}.
I'll note below where in the "Combine" function I get a mismatch error because of this implicit conversion. How might I go about accounting for this?
vFirstArray(1, 1)
vFirstArray(2, 1)
vFirstArray(3, 1)
vFirstArray(4, 1)
....
vFirstArrary(10, 1)
Say I have a second array of 2 elements that I want to combine: {vSecondArray of Type Variant/Variant(1 to 2, 1 to 1)
vSecondArray(1, 1)
vSecondArray(2, 1)
I can use a Function (see below) to Combine those two arrays - no problem, it works fine! However, if the second array is derived from a single cell the function breaks down as the second array is now {vSecondArray of Type Variant/Double}.
I'll note below where in the "Combine" function I get a mismatch error because of this implicit conversion. How might I go about accounting for this?
VBA Code:
Function Combine(A As Variant, B As Variant, Optional stacked As Boolean = True) As Variant
'assumes that A and B are 2-dimensional variant arrays
'if stacked is true then A is placed on top of B
'in this case the number of rows must be the same,
'otherwise they are placed side by side A|B
'in which case the number of columns are the same
'LBound can be anything but is assumed to be
'the same for A and B (in both dimensions)
'False is returned if a clash
Dim lb As Long, m_A As Long, n_A As Long
Dim m_B As Long, n_B As Long
Dim m As Long, N As Long
Dim i As Long, j As Long, k As Long
Dim C As Variant
If TypeName(A) = "Range" Then A = A.Value
If TypeName(B) = "Range" Then B = B.Value
lb = LBound(A, 1) 'CODE BREAKS HERE WITH ERROR MISMATCH
m_A = UBound(A, 1)
n_A = UBound(A, 2)
m_B = UBound(B, 1)
n_B = UBound(B, 2)
If stacked Then
m = m_A + m_B + 1 - lb
N = n_A
If n_B <> N Then
Combine = False
Exit Function
End If
Else
m = m_A
If m_B <> m Then
Combine = False
Exit Function
End If
N = n_A + n_B + 1 - lb
End If
ReDim C(lb To m, lb To N)
For i = lb To m
For j = lb To N
If stacked Then
If i <= m_A Then
C(i, j) = A(i, j)
Else
C(i, j) = B(lb + i - m_A - 1, j)
End If
Else
If j <= n_A Then
C(i, j) = A(i, j)
Else
C(i, j) = B(i, lb + j - n_A - 1)
End If
End If
Next j
Next i
Combine = C
End Function