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

#### RawlinsCross

##### Active Member
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``````

### 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
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
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
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

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
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

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``````

#### RawlinsCross

##### Active Member
Yep, that did the trick! Thanks for the assistance!

#### Akuini

##### Well-known Member
You're welcome, glad to help, & thanks for the feedback.

Replies
2
Views
155
Replies
6
Views
125
Replies
10
Views
425
Replies
1
Views
68
Replies
7
Views
223

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?

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