Generate series according to range and increment

[primeride2k]

I am working with a two page workbook. On page one I have upper an
lower values of a range (example 0.0 to 5.0, or 0 to 100) and a
increment value (example 0.2 or 5). On page two I would like a column t
automatically fill with the series of values within the range accordin
to the increment value.

For example: If the range on page one is from 0.0 to 5.0, and th
increment is 0.2, I would like a column in page two to fill 0.0, 0.2
0.4, 0.6, 0.8, 1.0, 1.2… …4.8, 5.0 in a column from top to bottom.

I hope this is clear enough; I am not very versed in Excel vocabulary
Any help is appreciated. Thanks

 

[Claus Busch]

Hi,

Am Thu, 16 May 2013 18:21:31 +0100 schrieb primeride2k:

I am working with a two page workbook. On page one I have upper and
lower values of a range (example 0.0 to 5.0, or 0 to 100) and an
increment value (example 0.2 or 5). On page two I would like a column to
automatically fill with the series of values within the range according
to the increment value.

Start value in Sheet1 A1, stopp value in A2, increment in B1:

Sub FillLinear()
With Sheets("Sheet2")
.Range("A1") = Sheets("Sheet1").Range("A1")
.Range("A1").DataSeries Rowcol:=xlColumns, Type:=xlLinear, _
Step:=Sheets("Sheet1").Range("B1"), _
Stop:=Sheets("Sheet1").Range("A2")
End With
End Sub


Regards
Claus Busch

 

[primeride2k]

Hi,

Am Thu, 16 May 2013 18:21:31 +0100 schrieb primeride2k:
-

Start value in Sheet1 A1, stopp value in A2, increment in B1:

Sub FillLinear()
With Sheets("Sheet2")
.Range("A1") = Sheets("Sheet1").Range("A1")
.Range("A1").DataSeries Rowcol:=xlColumns, Type:=xlLinear, _
Step:=Sheets("Sheet1").Range("B1"), _
Stop:=Sheets("Sheet1").Range("A2")
End With
End Sub


Regards
Claus Busch

This works great when I choose to run the macro from the drop down menu
How would I set it up so I can use it as a template? I would like t
open the excel file, input the range and increments, then have i
generate the series on sheet 2 automatically. Also, if I change th
range and/or increment on sheet 1, I would like the series to chang
accordingly on sheet 2 automatically.

Thanks for your help

 

[Claus Busch]

Hi,

Am Thu, 16 May 2013 20:32:15 +0100 schrieb primeride2k:

How would I set it up so I can use it as a template? I would like to
open the excel file, input the range and increments, then have it
generate the series on sheet 2 automatically. Also, if I change the
range and/or increment on sheet 1, I would like the series to change
accordingly on sheet 2 automatically.

right-click on tab of sheet1 => Show code => paste following code in the
code window:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A2,B1")) Is _
Nothing Then Exit Sub

With Sheets("Sheet2")
.Columns("A").ClearContents
.Range("A1") = Sheets("Sheet1").Range("A1")
.Range("A1").DataSeries Rowcol:=xlColumns, Type:=xlLinear, _
Step:=Sheets("Sheet1").Range("B1"), _
Stop:=Sheets("Sheet1").Range("A2")
End With
End Sub


Regards
Claus Busch

 

[primeride2k]

Hi,

Am Thu, 16 May 2013 20:32:15 +0100 schrieb primeride2k:
-

right-click on tab of sheet1 => Show code => paste following code i
the
code window:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A2,B1")) Is _
Nothing Then Exit Sub

With Sheets("Sheet2")
.Columns("A").ClearContents
.Range("A1") = Sheets("Sheet1").Range("A1")
.Range("A1").DataSeries Rowcol:=xlColumns, Type:=xlLinear, _
Step:=Sheets("Sheet1").Range("B1"), _
Stop:=Sheets("Sheet1").Range("A2")
End With
End Sub


Regards
Claus Busch

I am working on Excel for Mac 2011 version 14.1.0 and it does not giv
me an option for show code when I right click the sheet tab. Is ther
another place I can enter it? Thanks

 

[Claus Busch]

Hi,

Am Tue, 21 May 2013 17:29:45 +0100 schrieb primeride2k:

I am working on Excel for Mac 2011 version 14.1.0 and it does not give
me an option for show code when I right click the sheet tab. Is there
another place I can enter it? Thanks!

try Alt+F11


Regards
Claus Busch