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]

In what way is it not working?

 

[Ria_Ko]

In what way is it not working?

When i enter the function, it doesnt give me the required array elements. It gives "hey" in all the cells

 

[Ria_Ko]

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.

Hi Peter_SSs, i entered the formula in the first cell only still it does give me the required array elements . It gives me "hey" in all the cells.

 

[Fluff]

Change the last line of the code to

ShiftVector = B

Because you have a 2D array you do not need to transpose it.

 

[Ria_Ko]

Oh! Thanks alot i had been going crazy trying to figure out my mistake. Once again thank you so much Fluff! Means alot

 

[Fluff]

You're welcome & thanks for the feedback.

 

[akshat1231]

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

Hello! I have written the following code. The code is working but I am still not able to pass the assignment. I have been stuck on this for 1 week now. Can you tell me what might be the reason. I will be grateful.

Option Explicit
Option Base 1

Function ShiftVector(rng As Range, n As Integer) As Variant
Dim nr As Integer, i As Integer, B() As Variant
nr = rng.Rows.Count
ReDim B(nr) As Variant
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 = Application.WorksheetFunction.Transpose(B)

End Function

 

[Fluff]

Please start a new thread for this & give explain what you mean by "I am still not able to pass the assignment."