Combining 2D arrays based on single column range (single cell range issue)

[RawlinsCross]

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?

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

 

[Akuini]

I think you should check if A (& also B) has only 1 cell then redim A as 2d array, something like this:

Dim x
If TypeName(A) = "Range" Then
    If A.Cells.Count = 1 Then
        x = A.Value
        A = x
        ReDim A(1 To 1, 1 To 1)
        A(1, 1) = x
    End If
End If

 

[Akuini]

I forgot the ELSE part:

If TypeName(A) = "Range" Then
    If A.Cells.Count = 1 Then
        x = A.Value
        A = x
        ReDim A(1 To 1, 1 To 1)
        A(1, 1) = x
    Else
         A = A.Value
    End If
End If

 

[RawlinsCross]

Hi Akuini, how does that change if A and B are passed to the function as Arrays already? Would I have to not convert the ranges to arrays outside the function but purposely pass them in as ranges?

 

[Akuini]

I don't understand your question.
If A and B are passed to the function as Arrays then it will skip the IF statement.
Or
Do you mean what if the array is 1D array?

 

[RawlinsCross]

Prior to the function I move the ranges into Arrays. vArrayB is based on a range of 10 cells and becomes an (x, 1) based array, vArrayA is based on a range of 1 cell and becomes a "Double" which mucks things up. So should I pass the ranges instead of passing the arrays. Or maybe I need to Dim vArrayA and vArrayB as vArrayA() and vArrayB()?

Dim vArrayA As Variant
Dim vArrayB As Variant
Dim vArrayCombined As Variant

vArrayA = rRangeA.Value
vArrayB = rRangeB.Value
vArrayCombined = Combine(vArrayA, vArrayB, True)

 

[Akuini]

I think you should pass the arrays instead of the range. So the method I gave you above can be used before passing the arrays in the function:
Something like:

Dim vArrayA As Variant
Dim vArrayB As Variant
Dim vArrayCombined As Variant

    If rRangeA.Cells.Count = 1 Then
        ReDim vArrayA(1 To 1, 1 To 1)
        vArrayA(1, 1) = rRangeA.Value
    Else
         vArrayA = rRangeA.Value
    End If

vArrayB = rRangeB.Value
vArrayCombined = Combine(vArrayA, vArrayB, True)

then in the Function Combine you can remove the IF statement (because now you're sure the that vArrayA & vArrayB must be 2d array not a range)

   If TypeName(A) = "Range" Then A = A.Value
    If TypeName(B) = "Range" Then B = B.Value

 

[RawlinsCross]

Yep, that did the trick! Thanks for the assistance!

 

[Akuini]

You're welcome, glad to help, & thanks for the feedback.