VBA code to shift elements of an array by n

[Ria_Ko]

Hello everyone. Hope all are doing well.
I needed help in writing a VBA code with arguments for a range (rng) and n that will shift the elements of an (m x 1) range up by n rows. The first n rows of the range rng will “wrap around” and appear at the bottom of the resulting vector. If anybody could help me out with the code using the elements that i have dimmed so that its easy for me to understand, it would be of great help.
I have attached an image for reference.

Option Explicit
Option Base 1

Function ShiftVector (rng As Range, n As Integer)
Dim nr As Integer, B() As Variant, i As Integer
nr = rng.Rows.Count
ReDim B(nr, 1)
B = rng
If i > (nr - n) Then
i = i + 1

ShiftVector = WorksheetFunction.Transpose(B)
End Function

I tried this much but couldn't move ahead of this.
Many thanks,
Ria

 

[Fluff]

How about

Function ShiftVector(rng As Range, n As Integer)
Dim nr As Integer, B() As Variant, i As Integer
nr = rng.Rows.Count
ReDim B(1 To nr)
For i = 1 To nr
   If i <= n Then
      B(nr - n + i) = rng(i)
   Else
      B(i - n) = rng(i)
   End If
Next i
ShiftVector = WorksheetFunction.Transpose(B)
End Function

 

[Ria_Ko]

Hi Fluff, thanks alot for your help. The code worked.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Ria_Ko]

Can anybody help me out with the following code. What's wrong in it?

Option Explicit
Option Base 1

Function ShiftVector(rng As Range, n As Integer)
Dim i As Integer, B(), nr As Integer
nr = rng.Rows.Count
ReDim B(nr, 1)
For i = 1 To nr - n
B(i, 1) = rng.Cells(i + n, 1)
Next i
For i = nr - n + 1 To nr
B(i, 1) = rng.Cells(i + n - nr, 1)
Next i
ShiftVector = WorksheetFunction.Transpose(B)
End Function

 

[Fluff]

What is wrong with the code in post#2?

 

[Ria_Ko]

Hi Fluff, thanks for your reply. The above code in post 2 doesn’t work. I really dont know what is wrong in it.

 

[Fluff]

then why did you say it worked in post#3?

In what way does it not work?

+Fluff New.xlsm
	A	B	C
1	1	hey	hey
2	hi	3	3
3	5	9	9
4	hey	7	7
5	3	yo	yo
6	9	2	2
7	7	1	1
8	yo	hi	hi
9	2	5	5
List

Cell Formulas
Range	Formula
B1:B9	B1	=ShiftVector(A1:A9,3)
C1:C9	C1	=ShiftVector(A1:A9,3)
Press CTRL+SHIFT+ENTER to enter array formulas.
Dynamic array formulas.

 

[Peter_SSs]

FWIW, with Excel 365 with the SEQUENCE function, here is a non-looping one-liner that may do what you want?

Function ShiftVector(rng As Range, n As Integer)
  ShiftVector = Application.Index(rng, Evaluate("mod(sequence(" & rng.Rows.Count & ",," & n & ")," & rng.Rows.Count & ")+1"), 1)
End Function

Enter the formula in the first cell only

Ria_Ko 2020-05-14 1.xlsm
	A	B
1	a	d
2	b	e
3	c	f
4	d	g
5	e	a
6	f	b
7	g	c
Sheet1

Cell Formulas
Range	Formula
B1:B7	B1	=shiftvector(A1:A7,3)
Dynamic array formulas.

 

[Ria_Ko]

Hi Fluff, the code you gave worked very well! But the code that i have posted in post 5 is not working. So wanted to know what is wrong in it.