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

RawlinsCross

Active Member
Joined
Sep 9, 2016
Messages
332
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?

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
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,962
Office Version
  1. 365
Platform
  1. Windows
I think you should check if A (& also B) has only 1 cell then redim A as 2d array, something like this:
VBA Code:
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

Well-known Member
Joined
Feb 1, 2016
Messages
2,962
Office Version
  1. 365
Platform
  1. Windows
I forgot the ELSE part:
VBA Code:
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

Active Member
Joined
Sep 9, 2016
Messages
332
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

Well-known Member
Joined
Feb 1, 2016
Messages
2,962
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Active Member
Joined
Sep 9, 2016
Messages
332
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()?

VBA Code:
Dim vArrayA As Variant
Dim vArrayB As Variant
Dim vArrayCombined As Variant

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

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,962
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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:
VBA Code:
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)
VBA Code:
   If TypeName(A) = "Range" Then A = A.Value
    If TypeName(B) = "Range" Then B = B.Value
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,962
Office Version
  1. 365
Platform
  1. Windows
You're welcome, glad to help, & thanks for the feedback.:)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,852
Messages
5,627,255
Members
416,236
Latest member
Lynchbox

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top