Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generate list of numbers between start and end value
Hi,
This should be simple. I have a start value in one cell and end value in another. I want to generate a list between them. Start value: 8597000098881 End value: 8597000098890 Result: 8597000098881 8597000098882 8597000098883 8597000098884 8597000098885 8597000098886 8597000098887 8597000098888 8597000098889 8597000098890 Any idea? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generate list of numbers between start and end value
Startvalue in A1, Endvalue in B1
Sub myFill() [A1].AutoFill Destination:=Range("A1:A" & [B1] - [A1] + 1), Type:=xlFillSeries End Sub "Sinner" skrev: Hi, This should be simple. I have a start value in one cell and end value in another. I want to generate a list between them. Start value: 8597000098881 End value: 8597000098890 Result: 8597000098881 8597000098882 8597000098883 8597000098884 8597000098885 8597000098886 8597000098887 8597000098888 8597000098889 8597000098890 Any idea? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generate list of numbers between start and end value
Put the first number in one cell.
With that cell selected, hold the Ctrl key down and drag that cell down as far as you want. What you see will be in scientific notation because of the large number of digits but the numbers you want will be visible in the formula bar. HTH Otto "Sinner" wrote in message oups.com... Hi, This should be simple. I have a start value in one cell and end value in another. I want to generate a list between them. Start value: 8597000098881 End value: 8597000098890 Result: 8597000098881 8597000098882 8597000098883 8597000098884 8597000098885 8597000098886 8597000098887 8597000098888 8597000098889 8597000098890 Any idea? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generate list of numbers between start and end value
On Sep 18, 1:44 am, excelent
wrote: Startvalue in A1, Endvalue in B1 Sub myFill() [A1].AutoFill Destination:=Range("A1:A" & [B1] - [A1] + 1), Type:=xlFillSeries End Sub "Sinner" skrev: Hi, This should be simple. I have astartvaluein one cell and endvaluein another. I want togeneratealistbetweenthem. Startvalue: 8597000098881 Endvalue: 8597000098890 Result: 8597000098881 8597000098882 8597000098883 8597000098884 8597000098885 8597000098886 8597000098887 8597000098888 8597000098889 8597000098890 Any idea?- Hide quoted text - - Show quoted text - excelent this is giving an error. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generate list of numbers between start and end value
ok then try add sheet name - change Sheet1 to your sheetname
Sub myFill1() Sheets("Sheet1").Activate [A1].AutoFill Destination:=Range("A1:A" & [B1] - [A1] + 1), Type:=xlFillSeries End Sub or Sub myFill2() Set sh = Sheets("Sheet1") sh.Range("A1").AutoFill Destination:=sh.Range("A1:A" & sh.Range("B1") - sh.Range("A1") + 1), Type:=xlFillSeries End Sub "Sinner" skrev: On Sep 18, 1:44 am, excelent wrote: Startvalue in A1, Endvalue in B1 Sub myFill() [A1].AutoFill Destination:=Range("A1:A" & [B1] - [A1] + 1), Type:=xlFillSeries End Sub "Sinner" skrev: Hi, This should be simple. I have astartvaluein one cell and endvaluein another. I want togeneratealistbetweenthem. Startvalue: 8597000098881 Endvalue: 8597000098890 Result: 8597000098881 8597000098882 8597000098883 8597000098884 8597000098885 8597000098886 8597000098887 8597000098888 8597000098889 8597000098890 Any idea?- Hide quoted text - - Show quoted text - excelent this is giving an error. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generate list of numbers between start and end value
On Sep 18, 8:12 am, excelent
wrote: ok then try add sheet name - change Sheet1 to your sheetname Sub myFill1() Sheets("Sheet1").Activate [A1].AutoFill Destination:=Range("A1:A" & [B1] - [A1] + 1), Type:=xlFillSeries End Sub or Sub myFill2() Set sh = Sheets("Sheet1") sh.Range("A1").AutoFill Destination:=sh.Range("A1:A" & sh.Range("B1") - sh.Range("A1") + 1), Type:=xlFillSeries End Sub "Sinner" skrev: On Sep 18, 1:44 am, excelent wrote: Startvalue in A1, Endvalue in B1 Sub myFill() [A1].AutoFill Destination:=Range("A1:A" & [B1] - [A1] + 1), Type:=xlFillSeries End Sub "Sinner" skrev: Hi, This should be simple. I have astartvaluein one cell and endvaluein another. I want togeneratealistbetweenthem. Startvalue: 8597000098881 Endvalue: 8597000098890 Result: 8597000098881 8597000098882 8597000098883 8597000098884 8597000098885 8597000098886 8597000098887 8597000098888 8597000098889 8597000098890 Any idea?- Hide quoted text - - Show quoted text - excelent this is giving an error.- Hide quoted text - - Show quoted text - ------------------------------------------------------------------------------------ '_' was missing : ) now it is correct but still giving error. Sub myFill2() Set sh = Sheets("Sheet1") sh.Range("A1").AutoFill Destination:=sh.Range("A1:A" & sh.Range("B1") - _ sh.Range("A1") + 1), Type:=xlFillSeries End Sub It says, "Autofill method of range class failed." |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generate list of numbers between start and end value
I have a start value in one cell and end value in another.
I want to generate a list between them. Another option I like is "DataSeries." A1 = Start Number B1 = End Number Sub Demo() [A1].DataSeries _ Rowcol:=xlColumns, _ Type:=xlLinear, _ Date:=xlDay, _ Step:=1, _ Stop:=[B1], _ Trend:=False End Sub One advantage of DataSeries I believe is that the Algorithm is a little different. For example, if the step were other than 1 (say 0.1) you will have more "exact" values than if you drag down. There were a few old Microsoft kb articles on this. -- Dana DeLouis "Sinner" wrote in message oups.com... Hi, This should be simple. I have a start value in one cell and end value in another. I want to generate a list between them. Start value: 8597000098881 End value: 8597000098890 Result: 8597000098881 8597000098882 8597000098883 8597000098884 8597000098885 8597000098886 8597000098887 8597000098888 8597000098889 8597000098890 Any idea? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generate list of numbers between start and end value
On Sep 18, 10:51 am, "Dana DeLouis" wrote:
I have a start value in one cell and end value in another. I want to generate a list between them. Another option I like is "DataSeries." A1 = Start Number B1 = End Number Sub Demo() [A1].DataSeries _ Rowcol:=xlColumns, _ Type:=xlLinear, _ Date:=xlDay, _ Step:=1, _ Stop:=[B1], _ Trend:=False End Sub One advantage of DataSeries I believe is that the Algorithm is a little different. For example, if the step were other than 1 (say 0.1) you will have more "exact" values than if you drag down. There were a few old Microsoft kb articles on this. -- Dana DeLouis "Sinner" wrote in message oups.com... Hi, This should be simple. I have a start value in one cell and end value in another. I want to generate a list between them. Start value: 8597000098881 End value: 8597000098890 Result: 8597000098881 8597000098882 8597000098883 8597000098884 8597000098885 8597000098886 8597000098887 8597000098888 8597000098889 8597000098890 Any idea?- Hide quoted text - - Show quoted text - Dear Dana, This works but numbers are changed to scientific notation. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generate list of numbers between start and end value
Line 1. Sub myFill2() Line 2. Set sh = Sheets("Sheet1") Line 3. sh.Range("A1").AutoFill Destination:=sh.Range("A1:A" & sh.Range("B1") - sh.Range("A1") + 1), Type:=xlFillSeries Line 4. End Sub anyway here is anotner construction : Sub myFill3() Set sh = Sheets("Sheet1") rw = sh.Range("B1") - sh.Range("A1") + 1 sh.Range("A1").AutoFill Destination:=sh.Range("A1:A" & rw), Type:=xlFillSeries End Sub "Sinner" skrev: On Sep 18, 8:12 am, excelent wrote: ok then try add sheet name - change Sheet1 to your sheetname Sub myFill1() Sheets("Sheet1").Activate [A1].AutoFill Destination:=Range("A1:A" & [B1] - [A1] + 1), Type:=xlFillSeries End Sub or Sub myFill2() Set sh = Sheets("Sheet1") sh.Range("A1").AutoFill Destination:=sh.Range("A1:A" & sh.Range("B1") - sh.Range("A1") + 1), Type:=xlFillSeries End Sub "Sinner" skrev: On Sep 18, 1:44 am, excelent wrote: Startvalue in A1, Endvalue in B1 Sub myFill() [A1].AutoFill Destination:=Range("A1:A" & [B1] - [A1] + 1), Type:=xlFillSeries End Sub "Sinner" skrev: Hi, This should be simple. I have astartvaluein one cell and endvaluein another. I want togeneratealistbetweenthem. Startvalue: 8597000098881 Endvalue: 8597000098890 Result: 8597000098881 8597000098882 8597000098883 8597000098884 8597000098885 8597000098886 8597000098887 8597000098888 8597000098889 8597000098890 Any idea?- Hide quoted text - - Show quoted text - excelent this is giving an error.- Hide quoted text - - Show quoted text - ------------------------------------------------------------------------------------ '_' was missing : ) now it is correct but still giving error. Sub myFill2() Set sh = Sheets("Sheet1") sh.Range("A1").AutoFill Destination:=sh.Range("A1:A" & sh.Range("B1") - _ sh.Range("A1") + 1), Type:=xlFillSeries End Sub It says, "Autofill method of range class failed." |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generate list of numbers between start and end value
This works but numbers are changed to scientific notation.
Hi. Just a formatting fix. I guess with "Linear," we can remove the xlDay part. Sub Demo() [A1].DataSeries _ Rowcol:=xlColumns, _ Type:=xlLinear, _ Step:=1, _ Stop:=[B1], _ Trend:=False With [A1].EntireColumn .NumberFormat = "0" .AutoFit End With End Sub -- HTH :) Dana DeLouis "Sinner" wrote in message oups.com... On Sep 18, 10:51 am, "Dana DeLouis" wrote: I have a start value in one cell and end value in another. I want to generate a list between them. Another option I like is "DataSeries." A1 = Start Number B1 = End Number Sub Demo() [A1].DataSeries _ Rowcol:=xlColumns, _ Type:=xlLinear, _ Date:=xlDay, _ Step:=1, _ Stop:=[B1], _ Trend:=False End Sub One advantage of DataSeries I believe is that the Algorithm is a little different. For example, if the step were other than 1 (say 0.1) you will have more "exact" values than if you drag down. There were a few old Microsoft kb articles on this. -- Dana DeLouis "Sinner" wrote in message oups.com... Hi, This should be simple. I have a start value in one cell and end value in another. I want to generate a list between them. Start value: 8597000098881 End value: 8597000098890 Result: 8597000098881 8597000098882 8597000098883 8597000098884 8597000098885 8597000098886 8597000098887 8597000098888 8597000098889 8597000098890 Any idea?- Hide quoted text - - Show quoted text - Dear Dana, This works but numbers are changed to scientific notation. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generate list of numbers between start and end value
On Sep 18, 6:24 pm, excelent
wrote: Line 1. Sub myFill2() Line 2. Set sh = Sheets("Sheet1") Line 3. sh.Range("A1").AutoFill Destination:=sh.Range("A1:A" & sh.Range("B1") - sh.Range("A1") + 1), Type:=xlFillSeries Line 4. End Sub anyway here is anotner construction : Sub myFill3() Set sh = Sheets("Sheet1") rw = sh.Range("B1") - sh.Range("A1") + 1 sh.Range("A1").AutoFill Destination:=sh.Range("A1:A" & rw), Type:=xlFillSeries End Sub "Sinner" skrev: On Sep 18, 8:12 am, excelent wrote: ok then try add sheet name - change Sheet1 to your sheetname Sub myFill1() Sheets("Sheet1").Activate [A1].AutoFill Destination:=Range("A1:A" & [B1] - [A1] + 1), Type:=xlFillSeries End Sub or Sub myFill2() Set sh = Sheets("Sheet1") sh.Range("A1").AutoFill Destination:=sh.Range("A1:A" & sh.Range("B1") - sh.Range("A1") + 1), Type:=xlFillSeries End Sub "Sinner" skrev: On Sep 18, 1:44 am, excelent wrote: Startvalue in A1, Endvalue in B1 Sub myFill() [A1].AutoFill Destination:=Range("A1:A" & [B1] - [A1] + 1), Type:=xlFillSeries End Sub "Sinner" skrev: Hi, This should be simple. I have astartvaluein one cell and endvaluein another. I want togeneratealistbetweenthem. Startvalue: 8597000098881 Endvalue: 8597000098890 Result: 8597000098881 8597000098882 8597000098883 8597000098884 8597000098885 8597000098886 8597000098887 8597000098888 8597000098889 8597000098890 Any idea?- Hide quoted text - - Show quoted text - excelent this is giving an error.- Hide quoted text - - Show quoted text - ---------------------------------------------------------------------------*--------- '_' was missing : ) now it is correct but still giving error. Sub myFill2() Set sh = Sheets("Sheet1") sh.Range("A1").AutoFill Destination:=sh.Range("A1:A" & sh.Range("B1") - _ sh.Range("A1") + 1), Type:=xlFillSeries End Sub It says, "Autofill method of range class failed."- Hide quoted text - - Show quoted text - Excelent it still says "Autofill method of range class failed." |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generate list of numbers between start and end value
On Sep 18, 8:08 pm, "Dana DeLouis" wrote:
This works but numbers are changed to scientific notation. Hi. Just a formatting fix. I guess with "Linear," we can remove the xlDay part. Sub Demo() [A1].DataSeries _ Rowcol:=xlColumns, _ Type:=xlLinear, _ Step:=1, _ Stop:=[B1], _ Trend:=False With [A1].EntireColumn .NumberFormat = "0" .AutoFit End With End Sub -- HTH :) Dana DeLouis "Sinner" wrote in message oups.com... On Sep 18, 10:51 am, "Dana DeLouis" wrote: I have a start value in one cell and end value in another. I want to generate a list between them. Another option I like is "DataSeries." A1 = Start Number B1 = End Number Sub Demo() [A1].DataSeries _ Rowcol:=xlColumns, _ Type:=xlLinear, _ Date:=xlDay, _ Step:=1, _ Stop:=[B1], _ Trend:=False End Sub One advantage of DataSeries I believe is that the Algorithm is a little different. For example, if the step were other than 1 (say 0.1) you will have more "exact" values than if you drag down. There were a few old Microsoft kb articles on this. -- Dana DeLouis "Sinner" wrote in message groups.com... Hi, This should be simple. I have a start value in one cell and end value in another. I want to generate a list between them. Start value: 8597000098881 End value: 8597000098890 Result: 8597000098881 8597000098882 8597000098883 8597000098884 8597000098885 8597000098886 8597000098887 8597000098888 8597000098889 8597000098890 Any idea?- Hide quoted text - - Show quoted text - Dear Dana, This works but numbers are changed to scientific notation.- Hide quoted text - - Show quoted text - Dear Dana, Start Value is 8788007677800201790 End Value is 8788007677800201799 Not working for such length of figure. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generate list of numbers between start and end value
http://pmexcelent.dk/myFill.xls
"Sinner" skrev: On Sep 18, 6:24 pm, excelent wrote: Line 1. Sub myFill2() Line 2. Set sh = Sheets("Sheet1") Line 3. sh.Range("A1").AutoFill Destination:=sh.Range("A1:A" & sh.Range("B1") - sh.Range("A1") + 1), Type:=xlFillSeries Line 4. End Sub anyway here is anotner construction : Sub myFill3() Set sh = Sheets("Sheet1") rw = sh.Range("B1") - sh.Range("A1") + 1 sh.Range("A1").AutoFill Destination:=sh.Range("A1:A" & rw), Type:=xlFillSeries End Sub "Sinner" skrev: On Sep 18, 8:12 am, excelent wrote: ok then try add sheet name - change Sheet1 to your sheetname Sub myFill1() Sheets("Sheet1").Activate [A1].AutoFill Destination:=Range("A1:A" & [B1] - [A1] + 1), Type:=xlFillSeries End Sub or Sub myFill2() Set sh = Sheets("Sheet1") sh.Range("A1").AutoFill Destination:=sh.Range("A1:A" & sh.Range("B1") - sh.Range("A1") + 1), Type:=xlFillSeries End Sub "Sinner" skrev: On Sep 18, 1:44 am, excelent wrote: Startvalue in A1, Endvalue in B1 Sub myFill() [A1].AutoFill Destination:=Range("A1:A" & [B1] - [A1] + 1), Type:=xlFillSeries End Sub "Sinner" skrev: Hi, This should be simple. I have astartvaluein one cell and endvaluein another. I want togeneratealistbetweenthem. Startvalue: 8597000098881 Endvalue: 8597000098890 Result: 8597000098881 8597000098882 8597000098883 8597000098884 8597000098885 8597000098886 8597000098887 8597000098888 8597000098889 8597000098890 Any idea?- Hide quoted text - - Show quoted text - excelent this is giving an error.- Hide quoted text - - Show quoted text - ------------------------------------------------------------------------------------- '_' was missing : ) now it is correct but still giving error. Sub myFill2() Set sh = Sheets("Sheet1") sh.Range("A1").AutoFill Destination:=sh.Range("A1:A" & sh.Range("B1") - _ sh.Range("A1") + 1), Type:=xlFillSeries End Sub It says, "Autofill method of range class failed."- Hide quoted text - - Show quoted text - Excelent it still says "Autofill method of range class failed." |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generate list of numbers between start and end value
I have a start value in one cell and end value in another.
I want to generate a list between them. Start Value is 8788007677800201790 End Value is 8788007677800201799 How are you entering these "numbers" into Excel? Are these numbers entered as Strings? The reason I ask is that Excel is limited to 15 digits. However, there is a workaround. Do you want to have strings represent these numbers instead? vs Start value: 8597000098881 End value: 8597000098890 -- Dana DeLouis "Sinner" wrote in message ps.com... On Sep 18, 8:08 pm, "Dana DeLouis" wrote: This works but numbers are changed to scientific notation. Hi. Just a formatting fix. I guess with "Linear," we can remove the xlDay part. Sub Demo() [A1].DataSeries _ Rowcol:=xlColumns, _ Type:=xlLinear, _ Step:=1, _ Stop:=[B1], _ Trend:=False With [A1].EntireColumn .NumberFormat = "0" .AutoFit End With End Sub -- HTH :) Dana DeLouis "Sinner" wrote in message oups.com... On Sep 18, 10:51 am, "Dana DeLouis" wrote: I have a start value in one cell and end value in another. I want to generate a list between them. Another option I like is "DataSeries." A1 = Start Number B1 = End Number Sub Demo() [A1].DataSeries _ Rowcol:=xlColumns, _ Type:=xlLinear, _ Date:=xlDay, _ Step:=1, _ Stop:=[B1], _ Trend:=False End Sub One advantage of DataSeries I believe is that the Algorithm is a little different. For example, if the step were other than 1 (say 0.1) you will have more "exact" values than if you drag down. There were a few old Microsoft kb articles on this. -- Dana DeLouis "Sinner" wrote in message groups.com... Hi, This should be simple. I have a start value in one cell and end value in another. I want to generate a list between them. Start value: 8597000098881 End value: 8597000098890 Result: 8597000098881 8597000098882 8597000098883 8597000098884 8597000098885 8597000098886 8597000098887 8597000098888 8597000098889 8597000098890 Any idea?- Hide quoted text - - Show quoted text - Dear Dana, This works but numbers are changed to scientific notation.- Hide quoted text - - Show quoted text - Dear Dana, Start Value is 8788007677800201790 End Value is 8788007677800201799 Not working for such length of figure. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
GENERATE RANGES FROM GIVEN NUMBERS(START/END) AND COMPILE INTO ASINGLE LIST. | Excel Discussion (Misc queries) | |||
How generate new list in another worksheet from existing list? | Excel Worksheet Functions | |||
generate numbers | Excel Discussion (Misc queries) | |||
How to generate a list of randomly selected numbers within a range | Excel Worksheet Functions | |||
How do I generate a list of 5-digit numbers in Excel? | Excel Programming |