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

 

[Darren Bartrup]

Not sure what you're trying to achieve.

You've explained what you've got (I think), but not what you want to do with it.

 

[moonfish]

Why do you want to use VBA? You can achieve the same with excel formulas:

Excel Workbook
	A	B	C
1	number	side1	side2
2	1	1	*
3	2	2	*
4	3	3	*
5	4	4	*
6	5	5	*
7	6	*	10
8	7	*	9
9	8	*	8
10	9	*	7
11	10	*	6
12	11	11	*
13	12	12	*
14	13	13	*
15	14	14	*
16	15	15	*
17	16	*	20
18	17	*	19
19	18	*	18
20	19	*	17
21	20	*	16
Sheet1

Just copy the formulas in B2 and C2 down across your 49500 rows and you got what you want.

 

[Andrew Poulsom]

This worked for me:

Sub Test()
    Dim i As Long
    For i = 1 To 49500 Step 5
        If (i - 1) Mod 10 = 0 Then
            With Cells(i, 2)
                .Value = i
                .Resize(5).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
                    Step:=1, Stop:=i + 4, Trend:=False
            End With
        Else
            With Cells(i, 3)
                .Value = i + 4
                .Resize(5).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
                Step:=-1, Stop:=i, Trend:=False
            End With
        End If
    Next i
End Sub

 

[woods2007]

using office 2010

sorry my bad- what you can see is what I have done by hand (1-20) - i need to expand it all the way to 49,500. It will take hours type out and i know it can be done using a loop in vba- just don't know how to achieve it (doesn't help that the text version i pasted in looks nothing like the excel version and doesn't highlight the blank cells in the columns between the sets of numbers)
does this help clarify my query?

 

[woods2007]

That's worked brilliantly,

Cheers!

 

[Ruddles]

The two suggestions above should sort out your requirement.

As for posting worksheet extracts, next time try placing a border around the cells and selecting a small font - 8pt or 9pt.

 

[woods2007]

Just to make life difficult I've been asked to change it to the below, but this time just to 30,000.

how do i modify the script to achieve this?

Excel Workbook
	J	K	L
1	Number	side 1	side 2
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
WIP

Excel 2010

 

[Andrew Poulsom]

Should L7 really be 30,000?

 

[Andrew Poulsom]

In case the answer to my previous question is yes:

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

I have added some constants so that the code is a little more generic.