Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Generate series according to range and increment

16 views
Skip to first unread message

primeride2k

unread,
May 16, 2013, 1:21:31 PM5/16/13
to

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.

For example: If the range on page one is from 0.0 to 5.0, and the
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!




--
primeride2k

Claus Busch

unread,
May 16, 2013, 2:03:19 PM5/16/13
to
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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

primeride2k

unread,
May 16, 2013, 3:32:15 PM5/16/13
to

Claus Busch;1611881 Wrote:
> 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
> --
> Win XP PRof SP2 / Vista Ultimate SP2
> Office 2003 SP2 /2007 Ultimate SP2

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 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.

Thanks for your help!




--
primeride2k

Claus Busch

unread,
May 16, 2013, 3:57:51 PM5/16/13
to
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

primeride2k

unread,
May 21, 2013, 12:29:45 PM5/21/13
to

Claus Busch;1611885 Wrote:
> 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
> --
> Win XP PRof SP2 / Vista Ultimate SP2
> Office 2003 SP2 /2007 Ultimate SP2

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!




--
primeride2k

Claus Busch

unread,
May 21, 2013, 12:53:23 PM5/21/13
to
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
0 new messages