creating a range based on 2 values

[aa2105]

Hi

I have list of start and end points for particular locations from which I'm trying to create a list in increments of 5. For example the data i have is:

Location number Start Point End point
1 340 505
2 1040 1260
3 1420 1580

etc...

From this, how can i generate in column a series of points that start at 340 and end at 505 in increments of 5? And then do this for the second location (so from 1040 to 1260 again in increments of 5).

Any ideas would be much appreciated. Thank you.

 

[wigi]

For instance with a small macro that loops:

Sub incrementsof5()
    Range("E:E").ClearContents
    For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
        For j = Range("B" & i) To Range("C" & i) Step 5
            Range("E" & Rows.Count).End(xlUp).Offset(1).Value = j
        Next
    Next
End Sub

The output comes in column E. Change as suited.

Wigi

 

[MickG]

Try:-

[COLOR="Navy"]Sub[/COLOR] MG05May16
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] nRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & rows.Count).End(xlUp))
c = 3
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        c = c + 1
        [COLOR="Navy"]Set[/COLOR] nRng = Cells(1, c).Resize(Dn.Offset(, 2) - Dn.Offset(, 1))
        Cells(1, c) = Dn.Offset(, 1)
        nRng.DataSeries step:=5
    [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Data & Results:-

[COLOR="RoyalBlue"][B]Row No [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(A)    [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(B)      [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(C)    [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(D) [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(E) [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(F) [/B][/COLOR]
1.      Location   Start Point  End Point  340     1040    1420   
2.      1          340          505        345     1045    1425   
3.      2          1040         1260       350     1050    1430   
4.      3          1420         1580       355     1055    1435   
5.                                         360     1060    1440   
6.                                         365     1065    1445   
7.                                         370     1070    1450   
8.                                         375     1075    1455   
9.                                         380     1080    1460   
10.                                        385     1085    1465   
11.                                        Ditto   Ditto   Ditto

Regards Mick

 

[aa2105]

thanks Mick

 

[aa2105]

and Wigi!