How do you rotate an array?

ajwillshire

New Member
Hi,

I keep thinking this should be easy, but I'm not good at getting arrays into functions and doing things with them yet.

All I want (!) is a function of the style RotateArray(ArrayToBeRotated(), AmountToRotateItBy)

Can anyone help me?

Thanks in advance.

Cheers,

Andrew

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Can you define "rotate"? At first I thought you meant transpose, but the "AmountToRotateItBy" would seem to indicate otherwise. If you can do this with an example, that would help too.

Hi Seti,

What I mean is that if I had an input array of for example, months,

(Jan, Feb, Mar........Dec)

and I rotated it by 1, the output array would be

(Feb, Mar, Apr......Jan).

If I rotated it by 2, the output array would be

(Mar, Apr, May.....Feb).

Hope this clarifys my question.

Thanks,
Andrew

In case anyone is interested, here's how I did it in the end:

Function Rotate1DArray(aRotateMeRange As Range, iRotateBy As Integer) As Variant
Dim dSize As Double
Dim aOutput As Variant
Dim aIntermediate() As Variant
Dim aIntermediate2() As Variant
aIntermediate = aRotateMeRange.Value
dSize = UBound(aIntermediate, 2)
ReDim aIntermediate2(0 To (dSize - 1))
For i = 1 To dSize
aIntermediate2(i - 1) = aIntermediate(1, i)
Next
ReDim aOutput(0 To (dSize - 1))
For i = 0 To (dSize - 1)
aOutput(i) = aIntermediate2((i + iRotateBy) Mod dSize)
Next
Rotate1DArray = aOutput
End Function

It's probably not great code, but it takes a range, transforms it into an array, rotates it round by the specified amount and gives an array as an output.

Hopefully I'll adapt it to make it do more than a 1D Horizontal Array, but here it is for now.

And here's one that will rotate a 2D Array in either dimension. With comments!

Function Rotate2DArrayX(aRotateMeRange As Range, iVRotateBy As Integer, iHRotateBy As Integer) As Variant
On Error Resume Next

'Array sizing variables
Dim dHMax As Double
Dim dHMin As Double
Dim dVMax As Double
Dim dVMin As Double
Dim dHSize As Double
Dim dVSize As Double
'Output Arrays
Dim aOutput As Variant
Dim aIntermediate As Variant
Dim aIntermediate2 As Variant
'Turn Input Range into Array
aIntermediate = aRotateMeRange.Value
'Check that the rotate numbers are valid
If iVRotateBy < 0 Then Exit Function
If iHRotateBy < 0 Then Exit Function

'How big is the input Array?
'Horizontal Size
dHMax = UBound(aIntermediate, 2)
dHMin = LBound(aIntermediate, 2)
dHSize = dHMax - dHMin
'Vertical Size
dVMax = UBound(aIntermediate, 1)
dVMin = LBound(aIntermediate, 1)
dVSize = dVMax - dVMin
'Make the second intermediate array the right size. The second intermediate array is
'necessary in order to have an array running from 0 to N so that the Mod function works.

ReDim aIntermediate2(dVSize, dHSize)
For i = 0 To dVSize
For j = 0 To dHSize
aIntermediate2(i, j) = aIntermediate(i + 1, j + 1)
Next
Next
'Rotate Intermediate2 into Output Array
ReDim aOutput(dVSize, dHSize)
For i = 0 To dVSize
For j = 0 To dHSize
aOutput(i, j) = aIntermediate2(((i + iVRotateBy) Mod (dVSize + 1)), ((j + iHRotateBy) Mod (dHSize + 1)))

Next
Next

Rotate2DArrayX = aOutput

End Function

Replies
4
Views
190
Replies
3
Views
79
Replies
14
Views
403
Replies
3
Views
444
Replies
5
Views
210

Threads
1,207,110
Messages
6,076,604
Members
446,216
Latest member
BEEALTAIR

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

Disable AdBlock

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