Create a Spiral Number System

[birko61]

Hi,
can someone please help me create some VB code to do the following....

I want to create a circular number system, where 1 is in the middle of the page then each cell increases by 1 in like the following, then stopping at 1000.

13 14 15 16 17
12 03 04 05 18
11 02 01 06 19
10 09 08 07 20
25 24 23 22 21

Thanks in advance.
Birko

 

[Peter_SSs]

See if this is any use.

Sub Spiral()
   Dim Pos As Range
   Dim i As Long, rOffset As Long, cOffset As Long
   
   Application.ScreenUpdating = False
   Set Pos = Range("Z26") '<-- Starting cell - adjust to suit
   Pos.Value = 1
   Set Pos = Pos.Offset(0, -1)
   For i = 2 To 1000
      Pos.Value = i
      If Pos.Offset(, -1).Value = "" And Pos.Offset(-1).Value <> "" Then
         rOffset = 0: cOffset = -1
      ElseIf Pos.Offset(, -1).Value <> "" And Pos.Offset(1).Value = "" Then
         rOffset = 1: cOffset = 0
      ElseIf Pos.Offset(1).Value <> "" And Pos.Offset(, 1).Value = "" Then
         rOffset = 0: cOffset = 1
      ElseIf Pos.Offset(, 1).Value <> "" And Pos.Offset(-1).Value = "" Then
         rOffset = -1: cOffset = 0
      End If
      Set Pos = Pos.Offset(rOffset, cOffset)
   Next i
   Application.ScreenUpdating = True
End Sub

 

[birko61]

Thanks so much, it works perfectly.

regards
Birko

 

[James_B]

That's a really nice solution Peter.

 

[oogway]

How to use this code in excel total beginner
when i copy to excel module i can't make it to work

 

[oogway]

i want to make it Functions in circles like the numbers above and be able to change it

 

[Fluff]

Have another go, I've removed the formatting from the post

 

[oogway]

Have another go, I've removed the formatting from the post

can this code run as Functions not number like a1+1 and a2+1 and can be modified ?

 

[Fluff]

No idea. I would suggest that you start a thread of your own, giving exact details of what you are trying to achieve.