vba looping problem

[woods2007]

Can anyone help with the below problem?

I have three columns. col 'number' runs to 49,500

side 1 has numbers 1-5 followed by 5 blank cells followed by 11-15 looping through to 49,500

side 2 has the first 5 cells blank followed by numbers 10-6 desc followed by 5 blanks followed 20-16 looping through to 49,500.



Number side1 side2
1 1
2 2
3 3
4 4
5 5
6 10
7 9
8 8
9 7
10 6
11 11
12 12
13 13
14 14
15 15
16 20
17 19
18 18
19 17
20 16

I think a loop will do it but not sure how to go about it can anyone advise?

many thanks

 

[woods2007]

Sadly not. L7 should be 20. I have tried to show the pattern bellow, it needs to continue till it reaches 30,000. I hope it is clear. I'll be happy when I have this resolved!!!

Excel Workbook
	J	K	L
2	1	1
3	2	2
4	3	3
5	4	4
6	5	5
7	6		20
8	7		19
9	8		18
10	9		17
11	10		16
12	11	6
13	12	7
14	13	8
15	14	9
16	15	10
17	16		15
18	17		14
19	18		13
20	19		12
21	20		11
22	21	21
23	22	22
24	23	23
25	24	24
26	25	25
27	26		40
28	27		39
29	28		38
30	29		37
31	30		36
WIP

Excel 2010

 

[Andrew Poulsom]

Try:

Sub Test()
    Const Last As Long = 30000
    Const Skip As Long = 5
    Dim Toggle1 As Boolean
    Dim Toggle2 As Boolean
    Dim i As Long
    Toggle1 = True
    Toggle2 = True
    For i = 1 To Last Step Skip
        If (i - 1) Mod (Skip * 2) = 0 Then
            With Cells(i, 2)
                If Toggle1 = True Then
                    .Value = i
                    .Resize(5).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
                        Step:=1, Stop:=i + Skip - 1, Trend:=False
                    Toggle1 = False
                Else
                    .Value = i - Skip
                    .Resize(5).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
                        Step:=1, Stop:=i - 1, Trend:=False
                    Toggle1 = True
                End If
            End With
        Else
            With Cells(i, 3)
                If Toggle2 = True Then
                    .Value = i - 1 + (Skip * 3)
                    .Resize(5).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
                        Step:=-1, Stop:=i - Skip + (Skip * 3), Trend:=False
                    Toggle2 = False
                Else
                    .Value = i - 1
                    .Resize(5).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
                        Step:=-1, Stop:=i - Skip, Trend:=False
                    Toggle2 = True
                End If
            End With
        End If
    Next i
End Sub