Display value in column Array VBA-CodePudding

Could someone explain my why my code display values at columns instead of rows ? Thanks

Function ShiftVector(rng As Range, n As Integer)

Dim i As Integer
Dim j As Integer
Dim B As Variant
Dim A() As Variant
Dim nr As Integer
nr = rng.Rows.Count
ReDim B(nr)
ReDim A(nr)

For i = 1 To nr - n
    B(i) = rng(i   n)
Next i
For i = nr - n   1 To nr
    B(i) = rng(i - nr   n)
Next i

ShiftVector = B
End Function

CodePudding user response：

Your code loads a 1D array, which does not have rows by definition... Then, you do not explain what n means and I will make abstraction of its 'contribution' in the function... The next interpretation assumes that rng is a range containing only a column.

There are two ways of solving it:

Let the code as it is, but finally use:

ShiftVector = Application.Transpose(B)

ReDim and load a 2D array:

  ReDim B(1 to nr, 1 to 1)
  'and load it in the next way:
   B(i, 1) = rng(i   n)

You can place a range directly in a (2D) array:

  B = rng.value

If you will explain what n wants to be, I can adapt the answer to somehow take it in consideration...

Edited:

Please, play with the next function, able to make slices from a 2D array and 'mount' them in a different order:

Function ShiftVector2D(rng As Range, n As Integer) As Variant
 Dim nr As Long, arr, arrSl1, arrSl2

 nr = rng.rows.count - rng.row   1 'the number of the range rows, even if it does not start from the first row...
 arr = rng.Value                                 'place the range in a 2D array
 With Application
        arrSl1 = .Index(arr, Evaluate("row(1:" & n & ")"), 1)                         'obtain an array slice of the first n rows
        arrSl2 = .Index(arr, Evaluate("row(" & n   1 & ":" & nr & ")"), 1)    'obtain an array slice of the rows after n up to the last row
        arr = Split(Join(.Transpose(arrSl2), "|") & "|" & Join(.Transpose(arrSl1), "|"), "|") 'created a 1 D array by joinning the two arrays and split them by "|"
        ShiftVector2D = .Transpose(arr)   'return the 2D necessary shifted array
 End With
End Function

You can test it placing some strings in the range "A1:A10" and run the next code:

Sub testShiftVector2D()
  Dim rng As Range, arr
  Set rng = Range("A1:A10")

   arr = ShiftVector2D(rng, 4)
   Debug.Print Join(Application.Transpose(arr), "|")
End Sub