0
Public Function v2(LeftMatrix As Range, RightMatrix As Range) As Variant()

Dim e As Integer, s As Integer

e = LeftMatrix.Rows.Count
s = LeftMatrix.Columns.Count


ReDim m(1 To e, 1 To s + 1)

Dim i As Integer, j As Integer, k As Integer

For i = 1 To e
  For j = 1 To s + 1
    If j <= s Then
      m(i, j) = LeftMatrix(i, j)
    Else
      m(i, j) = RightMatrix(i, 1)
  Next j
Next i

For i = 1 To e
  For j = 1 To s + 1

    Cells(i, j) = m(i, j)

  Next j
Next i
End Function

I'm selecting two matrices from my worksheet and trying to merge them into a single matrix but I keep getting the ref error.

Solar Mike
  • 7,156
  • 4
  • 17
  • 32
My name
  • 21
  • 2
  • Are you trying to call this from a cell? – Warcupine Nov 02 '21 at 17:52
  • where do you initially declare `m` and what are `k_m` and `l_n`? – Scott Craner Nov 02 '21 at 17:53
  • yes, im selecting a cell in the worksheet – My name Nov 02 '21 at 17:54
  • Here is a function that does what you are trying to do: https://stackoverflow.com/questions/68655098/replicate-google-sheets-array1-array2-function-in-microsoft-excel-as-user-def/68655804#68655804 It works with any number of ranges. The only caveat is that they all must have the same number of columns. It takes any number of ranges and returns an array of those ranges stacked on top of each other. – Scott Craner Nov 02 '21 at 17:55
  • 1
    BTW, you cannot do: `Cells(i, j) = m(i, j)` in a function called from the worksheet. Function called from the work sheet cannot change the value of other cells. If you have office 365 just return the array itself and it will spill the results. But you need to `Dim` the array before you `ReDim`. – Scott Craner Nov 02 '21 at 18:17
  • Also a visual representation of what you have and what you are trying to achieve will help. – Scott Craner Nov 02 '21 at 18:19
  • 1
    BTW, if you have Office 365 the following formula will do what you want: `=LET(leftrng,$A$1:$D$17,rightrng,K1:K17,CHOOSE(INT(SEQUENCE(,COLUMNS(leftrng)+1,0)/COLUMNS(leftrng)+1),leftrng,rightrng))` Where `$A$1:$D$17` is the left range and `K1:K17` is the right range. – Scott Craner Nov 02 '21 at 18:30

0 Answers0