Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating number sequence
I'm using excel 2003
I would like to create a macro that is creating a number sequence (from 0 to 77777) only using the numbers 0 tru 7. So when I select a cel say A1 and I run the macro the first column needs to be filled with: 1, 2, 3, 4, 5, 6, 7, 10, 11, 12, 13, 14, 15, 16, 17, 20 etc etc etc. An other macro I would like to create is one that provides me with the following number sequence (lets say in column C) 0, 77, 100, 177, 200, 277, 300, 377, 400, 477, 500, 577 etc etc etc Can anybody help me with this? Regards, Paul |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating number sequence
Paul,
try this Sub dec2oct() x = 1 y = 0 Do Cells(x, 1).Value = Oct(x) Cells(x, 3).Value = y If x 1 Then If x Mod 2 = 0 Then Cells(x, 3).Value = Cells(x, 3).Offset(-1, 0).Value + 77 Else Cells(x, 3).Value = Cells(x, 3).Offset(-1, -0).Value + 23 End If End If x = x + 1 Loop Until Cells(x - 1, 1).Value = 7777 End Sub Mike "Paul Tikken" wrote: I'm using excel 2003 I would like to create a macro that is creating a number sequence (from 0 to 77777) only using the numbers 0 tru 7. So when I select a cel say A1 and I run the macro the first column needs to be filled with: 1, 2, 3, 4, 5, 6, 7, 10, 11, 12, 13, 14, 15, 16, 17, 20 etc etc etc. An other macro I would like to create is one that provides me with the following number sequence (lets say in column C) 0, 77, 100, 177, 200, 277, 300, 377, 400, 477, 500, 577 etc etc etc Can anybody help me with this? Regards, Paul |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating number sequence
Mike,
Works great, but I forgot to mention 1 more thing. Similar to the first sequence, the number 8 and 9 can't be used in the second sequence as wel. So you will get a similar jump from 777 to 1000; 500, 577, 600, 677, 700, 777, 1000, 1077, 1100, 1177 etc etc. Any ideas? Paul "Mike H" wrote: Paul, try this Sub dec2oct() x = 1 y = 0 Do Cells(x, 1).Value = Oct(x) Cells(x, 3).Value = y If x 1 Then If x Mod 2 = 0 Then Cells(x, 3).Value = Cells(x, 3).Offset(-1, 0).Value + 77 Else Cells(x, 3).Value = Cells(x, 3).Offset(-1, -0).Value + 23 End If End If x = x + 1 Loop Until Cells(x - 1, 1).Value = 7777 End Sub Mike "Paul Tikken" wrote: I'm using excel 2003 I would like to create a macro that is creating a number sequence (from 0 to 77777) only using the numbers 0 tru 7. So when I select a cel say A1 and I run the macro the first column needs to be filled with: 1, 2, 3, 4, 5, 6, 7, 10, 11, 12, 13, 14, 15, 16, 17, 20 etc etc etc. An other macro I would like to create is one that provides me with the following number sequence (lets say in column C) 0, 77, 100, 177, 200, 277, 300, 377, 400, 477, 500, 577 etc etc etc Can anybody help me with this? Regards, Paul |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating number sequence
Paul,
Yes you did neglect to note that rather crucial point!! Too late for me now, if it isn't solved by morning I'll have a look then. Mike "Paul Tikken" wrote: Mike, Works great, but I forgot to mention 1 more thing. Similar to the first sequence, the number 8 and 9 can't be used in the second sequence as wel. So you will get a similar jump from 777 to 1000; 500, 577, 600, 677, 700, 777, 1000, 1077, 1100, 1177 etc etc. Any ideas? Paul "Mike H" wrote: Paul, try this Sub dec2oct() x = 1 y = 0 Do Cells(x, 1).Value = Oct(x) Cells(x, 3).Value = y If x 1 Then If x Mod 2 = 0 Then Cells(x, 3).Value = Cells(x, 3).Offset(-1, 0).Value + 77 Else Cells(x, 3).Value = Cells(x, 3).Offset(-1, -0).Value + 23 End If End If x = x + 1 Loop Until Cells(x - 1, 1).Value = 7777 End Sub Mike "Paul Tikken" wrote: I'm using excel 2003 I would like to create a macro that is creating a number sequence (from 0 to 77777) only using the numbers 0 tru 7. So when I select a cel say A1 and I run the macro the first column needs to be filled with: 1, 2, 3, 4, 5, 6, 7, 10, 11, 12, 13, 14, 15, 16, 17, 20 etc etc etc. An other macro I would like to create is one that provides me with the following number sequence (lets say in column C) 0, 77, 100, 177, 200, 277, 300, 377, 400, 477, 500, 577 etc etc etc Can anybody help me with this? Regards, Paul |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating number sequence
Tanks Mike, appreciate it!
"Mike H" wrote: Paul, Yes you did neglect to note that rather crucial point!! Too late for me now, if it isn't solved by morning I'll have a look then. Mike "Paul Tikken" wrote: Mike, Works great, but I forgot to mention 1 more thing. Similar to the first sequence, the number 8 and 9 can't be used in the second sequence as wel. So you will get a similar jump from 777 to 1000; 500, 577, 600, 677, 700, 777, 1000, 1077, 1100, 1177 etc etc. Any ideas? Paul "Mike H" wrote: Paul, try this Sub dec2oct() x = 1 y = 0 Do Cells(x, 1).Value = Oct(x) Cells(x, 3).Value = y If x 1 Then If x Mod 2 = 0 Then Cells(x, 3).Value = Cells(x, 3).Offset(-1, 0).Value + 77 Else Cells(x, 3).Value = Cells(x, 3).Offset(-1, -0).Value + 23 End If End If x = x + 1 Loop Until Cells(x - 1, 1).Value = 7777 End Sub Mike "Paul Tikken" wrote: I'm using excel 2003 I would like to create a macro that is creating a number sequence (from 0 to 77777) only using the numbers 0 tru 7. So when I select a cel say A1 and I run the macro the first column needs to be filled with: 1, 2, 3, 4, 5, 6, 7, 10, 11, 12, 13, 14, 15, 16, 17, 20 etc etc etc. An other macro I would like to create is one that provides me with the following number sequence (lets say in column C) 0, 77, 100, 177, 200, 277, 300, 377, 400, 477, 500, 577 etc etc etc Can anybody help me with this? Regards, Paul |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating number sequence
First macro.....................
Sub Fill_Nums() With Range("A1") .FormulaR1C1 = "1" .Select Selection.AutoFill Destination:=Range("A1:A65536"), _ Type:=xlFillSeries End With End Sub Cannot provide a macro for second scenario. I do not understand the sequence. Gord Dibben MS Excel MVP On Thu, 31 Jan 2008 11:41:05 -0800, Paul Tikken wrote: I'm using excel 2003 I would like to create a macro that is creating a number sequence (from 0 to 77777) only using the numbers 0 tru 7. So when I select a cel say A1 and I run the macro the first column needs to be filled with: 1, 2, 3, 4, 5, 6, 7, 10, 11, 12, 13, 14, 15, 16, 17, 20 etc etc etc. An other macro I would like to create is one that provides me with the following number sequence (lets say in column C) 0, 77, 100, 177, 200, 277, 300, 377, 400, 477, 500, 577 etc etc etc Can anybody help me with this? Regards, Paul |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating number sequence
Gord,
Thanks, but the macro Mike gave me is working fine for the first scenario, but the second scenario I forgot to mention that this sequence only can contain the digits 0 tru 7. So the sequence will be; 0, 77, 100, 177, 200, 277, 300, 377, 400, 477, 500, 577, 600, 677, 700, 777, 1000, 1077, 1100, 1177 etc etc etc Any clue? Paul "Gord Dibben" wrote: First macro..................... Sub Fill_Nums() With Range("A1") .FormulaR1C1 = "1" .Select Selection.AutoFill Destination:=Range("A1:A65536"), _ Type:=xlFillSeries End With End Sub Cannot provide a macro for second scenario. I do not understand the sequence. Gord Dibben MS Excel MVP On Thu, 31 Jan 2008 11:41:05 -0800, Paul Tikken wrote: I'm using excel 2003 I would like to create a macro that is creating a number sequence (from 0 to 77777) only using the numbers 0 tru 7. So when I select a cel say A1 and I run the macro the first column needs to be filled with: 1, 2, 3, 4, 5, 6, 7, 10, 11, 12, 13, 14, 15, 16, 17, 20 etc etc etc. An other macro I would like to create is one that provides me with the following number sequence (lets say in column C) 0, 77, 100, 177, 200, 277, 300, 377, 400, 477, 500, 577 etc etc etc Can anybody help me with this? Regards, Paul |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating number sequence
Paul,
Another attempt at Octal Sub dec2oct() x = 1 Do Cells(x, 1).Value = Oct(x) x = x + 1 Loop Until Cells(x - 1, 1).Value = 777 y = 1 a = 0 Do Cells(y, 3).Value = Oct(a) Cells(y + 1, 3).Value = Oct(a + 63) y = y + 2 a = a + 64 Loop Until Cells(y, 1).Value = "" End Sub Mike "Paul Tikken" wrote: Gord, Thanks, but the macro Mike gave me is working fine for the first scenario, but the second scenario I forgot to mention that this sequence only can contain the digits 0 tru 7. So the sequence will be; 0, 77, 100, 177, 200, 277, 300, 377, 400, 477, 500, 577, 600, 677, 700, 777, 1000, 1077, 1100, 1177 etc etc etc Any clue? Paul "Gord Dibben" wrote: First macro..................... Sub Fill_Nums() With Range("A1") .FormulaR1C1 = "1" .Select Selection.AutoFill Destination:=Range("A1:A65536"), _ Type:=xlFillSeries End With End Sub Cannot provide a macro for second scenario. I do not understand the sequence. Gord Dibben MS Excel MVP On Thu, 31 Jan 2008 11:41:05 -0800, Paul Tikken wrote: I'm using excel 2003 I would like to create a macro that is creating a number sequence (from 0 to 77777) only using the numbers 0 tru 7. So when I select a cel say A1 and I run the macro the first column needs to be filled with: 1, 2, 3, 4, 5, 6, 7, 10, 11, 12, 13, 14, 15, 16, 17, 20 etc etc etc. An other macro I would like to create is one that provides me with the following number sequence (lets say in column C) 0, 77, 100, 177, 200, 277, 300, 377, 400, 477, 500, 577 etc etc etc Can anybody help me with this? Regards, Paul |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating number sequence
Paul,
a bit quicker and now a single loop Sub dec2oct() x = 1 a = 0 Do Cells(x, 1).Value = Oct(x) Cells(x + 1, 1).Value = Oct(x + 1) Cells(x, 3).Value = Oct(a) Cells(x + 1, 3).Value = Oct(a + 63) a = a + 64 x = x + 2 Loop Until Cells(x - 2, 1).Value = 7777 End Sub Mike "Mike H" wrote: Paul, Another attempt at Octal Sub dec2oct() x = 1 Do Cells(x, 1).Value = Oct(x) x = x + 1 Loop Until Cells(x - 1, 1).Value = 777 y = 1 a = 0 Do Cells(y, 3).Value = Oct(a) Cells(y + 1, 3).Value = Oct(a + 63) y = y + 2 a = a + 64 Loop Until Cells(y, 1).Value = "" End Sub Mike "Paul Tikken" wrote: Gord, Thanks, but the macro Mike gave me is working fine for the first scenario, but the second scenario I forgot to mention that this sequence only can contain the digits 0 tru 7. So the sequence will be; 0, 77, 100, 177, 200, 277, 300, 377, 400, 477, 500, 577, 600, 677, 700, 777, 1000, 1077, 1100, 1177 etc etc etc Any clue? Paul "Gord Dibben" wrote: First macro..................... Sub Fill_Nums() With Range("A1") .FormulaR1C1 = "1" .Select Selection.AutoFill Destination:=Range("A1:A65536"), _ Type:=xlFillSeries End With End Sub Cannot provide a macro for second scenario. I do not understand the sequence. Gord Dibben MS Excel MVP On Thu, 31 Jan 2008 11:41:05 -0800, Paul Tikken wrote: I'm using excel 2003 I would like to create a macro that is creating a number sequence (from 0 to 77777) only using the numbers 0 tru 7. So when I select a cel say A1 and I run the macro the first column needs to be filled with: 1, 2, 3, 4, 5, 6, 7, 10, 11, 12, 13, 14, 15, 16, 17, 20 etc etc etc. An other macro I would like to create is one that provides me with the following number sequence (lets say in column C) 0, 77, 100, 177, 200, 277, 300, 377, 400, 477, 500, 577 etc etc etc Can anybody help me with this? Regards, Paul |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating number sequence
Mike,
Works like a charm!!! Thank you very much!! Mike, you might help me with another sequence. This is a combination of Alphanumeric and numeric characters. The sequence starts with; AA000, AA077, AA100, AA177 etc etc up to AA777 after this the sequence needs to proceed with AB000, AB077, AB100, AB177 up to AB777 then proceding with AC000 etc etc etc all the way up to AZ777 after this the sequence needs to proceed with BA000, BA077, etc etc the sequence needs to work his way up to ZZ777. All alphanumeric characters (A-Z) can be used and only numeric characters 0 tru 7. This can be a seperate macro for column E. Thanks again!! Paul "Mike H" wrote: Paul, Another attempt at Octal Sub dec2oct() x = 1 Do Cells(x, 1).Value = Oct(x) x = x + 1 Loop Until Cells(x - 1, 1).Value = 777 y = 1 a = 0 Do Cells(y, 3).Value = Oct(a) Cells(y + 1, 3).Value = Oct(a + 63) y = y + 2 a = a + 64 Loop Until Cells(y, 1).Value = "" End Sub Mike "Paul Tikken" wrote: Gord, Thanks, but the macro Mike gave me is working fine for the first scenario, but the second scenario I forgot to mention that this sequence only can contain the digits 0 tru 7. So the sequence will be; 0, 77, 100, 177, 200, 277, 300, 377, 400, 477, 500, 577, 600, 677, 700, 777, 1000, 1077, 1100, 1177 etc etc etc Any clue? Paul "Gord Dibben" wrote: First macro..................... Sub Fill_Nums() With Range("A1") .FormulaR1C1 = "1" .Select Selection.AutoFill Destination:=Range("A1:A65536"), _ Type:=xlFillSeries End With End Sub Cannot provide a macro for second scenario. I do not understand the sequence. Gord Dibben MS Excel MVP On Thu, 31 Jan 2008 11:41:05 -0800, Paul Tikken wrote: I'm using excel 2003 I would like to create a macro that is creating a number sequence (from 0 to 77777) only using the numbers 0 tru 7. So when I select a cel say A1 and I run the macro the first column needs to be filled with: 1, 2, 3, 4, 5, 6, 7, 10, 11, 12, 13, 14, 15, 16, 17, 20 etc etc etc. An other macro I would like to create is one that provides me with the following number sequence (lets say in column C) 0, 77, 100, 177, 200, 277, 300, 377, 400, 477, 500, 577 etc etc etc Can anybody help me with this? Regards, Paul |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating number sequence
Possibly,
Sub dec2oct() q = 1 a = 0 For b = 65 To 90 For c = 65 To 90 For x = 1 To Oct(14) Step 2 Cells(q, 3).Value = Chr(b) & Chr(c) & Oct(a) Cells(q + 1, 3).Value = Chr(b) & Chr(c) & Oct(a + 63) a = a + 64 q = q + 2 Next a = 0 Next Next End Sub Mike "Paul Tikken" wrote: Mike, Works like a charm!!! Thank you very much!! Mike, you might help me with another sequence. This is a combination of Alphanumeric and numeric characters. The sequence starts with; AA000, AA077, AA100, AA177 etc etc up to AA777 after this the sequence needs to proceed with AB000, AB077, AB100, AB177 up to AB777 then proceding with AC000 etc etc etc all the way up to AZ777 after this the sequence needs to proceed with BA000, BA077, etc etc the sequence needs to work his way up to ZZ777. All alphanumeric characters (A-Z) can be used and only numeric characters 0 tru 7. This can be a seperate macro for column E. Thanks again!! Paul "Mike H" wrote: Paul, Another attempt at Octal Sub dec2oct() x = 1 Do Cells(x, 1).Value = Oct(x) x = x + 1 Loop Until Cells(x - 1, 1).Value = 777 y = 1 a = 0 Do Cells(y, 3).Value = Oct(a) Cells(y + 1, 3).Value = Oct(a + 63) y = y + 2 a = a + 64 Loop Until Cells(y, 1).Value = "" End Sub Mike "Paul Tikken" wrote: Gord, Thanks, but the macro Mike gave me is working fine for the first scenario, but the second scenario I forgot to mention that this sequence only can contain the digits 0 tru 7. So the sequence will be; 0, 77, 100, 177, 200, 277, 300, 377, 400, 477, 500, 577, 600, 677, 700, 777, 1000, 1077, 1100, 1177 etc etc etc Any clue? Paul "Gord Dibben" wrote: First macro..................... Sub Fill_Nums() With Range("A1") .FormulaR1C1 = "1" .Select Selection.AutoFill Destination:=Range("A1:A65536"), _ Type:=xlFillSeries End With End Sub Cannot provide a macro for second scenario. I do not understand the sequence. Gord Dibben MS Excel MVP On Thu, 31 Jan 2008 11:41:05 -0800, Paul Tikken wrote: I'm using excel 2003 I would like to create a macro that is creating a number sequence (from 0 to 77777) only using the numbers 0 tru 7. So when I select a cel say A1 and I run the macro the first column needs to be filled with: 1, 2, 3, 4, 5, 6, 7, 10, 11, 12, 13, 14, 15, 16, 17, 20 etc etc etc. An other macro I would like to create is one that provides me with the following number sequence (lets say in column C) 0, 77, 100, 177, 200, 277, 300, 377, 400, 477, 500, 577 etc etc etc Can anybody help me with this? Regards, Paul |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating number sequence
OOPS,
Formatting error Sub dec2oct() q = 1 a = 0 For b = 65 To 90 For c = 65 To 90 For x = 1 To Oct(14) Step 2 Cells(q, 3).Value = Chr(b) & Chr(c) & Format(Oct(a), "000") Cells(q + 1, 3).Value = Chr(b) & Chr(c) & Format(Oct(a + 63), "000") a = a + 64 q = q + 2 Next a = 0 Next Next End Sub Mike "Mike H" wrote: Possibly, Sub dec2oct() q = 1 a = 0 For b = 65 To 90 For c = 65 To 90 For x = 1 To Oct(14) Step 2 Cells(q, 3).Value = Chr(b) & Chr(c) & Oct(a) Cells(q + 1, 3).Value = Chr(b) & Chr(c) & Oct(a + 63) a = a + 64 q = q + 2 Next a = 0 Next Next End Sub Mike "Paul Tikken" wrote: Mike, Works like a charm!!! Thank you very much!! Mike, you might help me with another sequence. This is a combination of Alphanumeric and numeric characters. The sequence starts with; AA000, AA077, AA100, AA177 etc etc up to AA777 after this the sequence needs to proceed with AB000, AB077, AB100, AB177 up to AB777 then proceding with AC000 etc etc etc all the way up to AZ777 after this the sequence needs to proceed with BA000, BA077, etc etc the sequence needs to work his way up to ZZ777. All alphanumeric characters (A-Z) can be used and only numeric characters 0 tru 7. This can be a seperate macro for column E. Thanks again!! Paul "Mike H" wrote: Paul, Another attempt at Octal Sub dec2oct() x = 1 Do Cells(x, 1).Value = Oct(x) x = x + 1 Loop Until Cells(x - 1, 1).Value = 777 y = 1 a = 0 Do Cells(y, 3).Value = Oct(a) Cells(y + 1, 3).Value = Oct(a + 63) y = y + 2 a = a + 64 Loop Until Cells(y, 1).Value = "" End Sub Mike "Paul Tikken" wrote: Gord, Thanks, but the macro Mike gave me is working fine for the first scenario, but the second scenario I forgot to mention that this sequence only can contain the digits 0 tru 7. So the sequence will be; 0, 77, 100, 177, 200, 277, 300, 377, 400, 477, 500, 577, 600, 677, 700, 777, 1000, 1077, 1100, 1177 etc etc etc Any clue? Paul "Gord Dibben" wrote: First macro..................... Sub Fill_Nums() With Range("A1") .FormulaR1C1 = "1" .Select Selection.AutoFill Destination:=Range("A1:A65536"), _ Type:=xlFillSeries End With End Sub Cannot provide a macro for second scenario. I do not understand the sequence. Gord Dibben MS Excel MVP On Thu, 31 Jan 2008 11:41:05 -0800, Paul Tikken wrote: I'm using excel 2003 I would like to create a macro that is creating a number sequence (from 0 to 77777) only using the numbers 0 tru 7. So when I select a cel say A1 and I run the macro the first column needs to be filled with: 1, 2, 3, 4, 5, 6, 7, 10, 11, 12, 13, 14, 15, 16, 17, 20 etc etc etc. An other macro I would like to create is one that provides me with the following number sequence (lets say in column C) 0, 77, 100, 177, 200, 277, 300, 377, 400, 477, 500, 577 etc etc etc Can anybody help me with this? Regards, Paul |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating number sequence
Mike,
It works great, thank you very much!!! Paul "Mike H" wrote: OOPS, Formatting error Sub dec2oct() q = 1 a = 0 For b = 65 To 90 For c = 65 To 90 For x = 1 To Oct(14) Step 2 Cells(q, 3).Value = Chr(b) & Chr(c) & Format(Oct(a), "000") Cells(q + 1, 3).Value = Chr(b) & Chr(c) & Format(Oct(a + 63), "000") a = a + 64 q = q + 2 Next a = 0 Next Next End Sub Mike "Mike H" wrote: Possibly, Sub dec2oct() q = 1 a = 0 For b = 65 To 90 For c = 65 To 90 For x = 1 To Oct(14) Step 2 Cells(q, 3).Value = Chr(b) & Chr(c) & Oct(a) Cells(q + 1, 3).Value = Chr(b) & Chr(c) & Oct(a + 63) a = a + 64 q = q + 2 Next a = 0 Next Next End Sub Mike "Paul Tikken" wrote: Mike, Works like a charm!!! Thank you very much!! Mike, you might help me with another sequence. This is a combination of Alphanumeric and numeric characters. The sequence starts with; AA000, AA077, AA100, AA177 etc etc up to AA777 after this the sequence needs to proceed with AB000, AB077, AB100, AB177 up to AB777 then proceding with AC000 etc etc etc all the way up to AZ777 after this the sequence needs to proceed with BA000, BA077, etc etc the sequence needs to work his way up to ZZ777. All alphanumeric characters (A-Z) can be used and only numeric characters 0 tru 7. This can be a seperate macro for column E. Thanks again!! Paul "Mike H" wrote: Paul, Another attempt at Octal Sub dec2oct() x = 1 Do Cells(x, 1).Value = Oct(x) x = x + 1 Loop Until Cells(x - 1, 1).Value = 777 y = 1 a = 0 Do Cells(y, 3).Value = Oct(a) Cells(y + 1, 3).Value = Oct(a + 63) y = y + 2 a = a + 64 Loop Until Cells(y, 1).Value = "" End Sub Mike "Paul Tikken" wrote: Gord, Thanks, but the macro Mike gave me is working fine for the first scenario, but the second scenario I forgot to mention that this sequence only can contain the digits 0 tru 7. So the sequence will be; 0, 77, 100, 177, 200, 277, 300, 377, 400, 477, 500, 577, 600, 677, 700, 777, 1000, 1077, 1100, 1177 etc etc etc Any clue? Paul "Gord Dibben" wrote: First macro..................... Sub Fill_Nums() With Range("A1") .FormulaR1C1 = "1" .Select Selection.AutoFill Destination:=Range("A1:A65536"), _ Type:=xlFillSeries End With End Sub Cannot provide a macro for second scenario. I do not understand the sequence. Gord Dibben MS Excel MVP On Thu, 31 Jan 2008 11:41:05 -0800, Paul Tikken wrote: I'm using excel 2003 I would like to create a macro that is creating a number sequence (from 0 to 77777) only using the numbers 0 tru 7. So when I select a cel say A1 and I run the macro the first column needs to be filled with: 1, 2, 3, 4, 5, 6, 7, 10, 11, 12, 13, 14, 15, 16, 17, 20 etc etc etc. An other macro I would like to create is one that provides me with the following number sequence (lets say in column C) 0, 77, 100, 177, 200, 277, 300, 377, 400, 477, 500, 577 etc etc etc Can anybody help me with this? Regards, Paul |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating number sequence
Your welcome, it was an interesting one to have a got at
"Paul Tikken" wrote: Mike, It works great, thank you very much!!! Paul "Mike H" wrote: OOPS, Formatting error Sub dec2oct() q = 1 a = 0 For b = 65 To 90 For c = 65 To 90 For x = 1 To Oct(14) Step 2 Cells(q, 3).Value = Chr(b) & Chr(c) & Format(Oct(a), "000") Cells(q + 1, 3).Value = Chr(b) & Chr(c) & Format(Oct(a + 63), "000") a = a + 64 q = q + 2 Next a = 0 Next Next End Sub Mike "Mike H" wrote: Possibly, Sub dec2oct() q = 1 a = 0 For b = 65 To 90 For c = 65 To 90 For x = 1 To Oct(14) Step 2 Cells(q, 3).Value = Chr(b) & Chr(c) & Oct(a) Cells(q + 1, 3).Value = Chr(b) & Chr(c) & Oct(a + 63) a = a + 64 q = q + 2 Next a = 0 Next Next End Sub Mike "Paul Tikken" wrote: Mike, Works like a charm!!! Thank you very much!! Mike, you might help me with another sequence. This is a combination of Alphanumeric and numeric characters. The sequence starts with; AA000, AA077, AA100, AA177 etc etc up to AA777 after this the sequence needs to proceed with AB000, AB077, AB100, AB177 up to AB777 then proceding with AC000 etc etc etc all the way up to AZ777 after this the sequence needs to proceed with BA000, BA077, etc etc the sequence needs to work his way up to ZZ777. All alphanumeric characters (A-Z) can be used and only numeric characters 0 tru 7. This can be a seperate macro for column E. Thanks again!! Paul "Mike H" wrote: Paul, Another attempt at Octal Sub dec2oct() x = 1 Do Cells(x, 1).Value = Oct(x) x = x + 1 Loop Until Cells(x - 1, 1).Value = 777 y = 1 a = 0 Do Cells(y, 3).Value = Oct(a) Cells(y + 1, 3).Value = Oct(a + 63) y = y + 2 a = a + 64 Loop Until Cells(y, 1).Value = "" End Sub Mike "Paul Tikken" wrote: Gord, Thanks, but the macro Mike gave me is working fine for the first scenario, but the second scenario I forgot to mention that this sequence only can contain the digits 0 tru 7. So the sequence will be; 0, 77, 100, 177, 200, 277, 300, 377, 400, 477, 500, 577, 600, 677, 700, 777, 1000, 1077, 1100, 1177 etc etc etc Any clue? Paul "Gord Dibben" wrote: First macro..................... Sub Fill_Nums() With Range("A1") .FormulaR1C1 = "1" .Select Selection.AutoFill Destination:=Range("A1:A65536"), _ Type:=xlFillSeries End With End Sub Cannot provide a macro for second scenario. I do not understand the sequence. Gord Dibben MS Excel MVP On Thu, 31 Jan 2008 11:41:05 -0800, Paul Tikken wrote: I'm using excel 2003 I would like to create a macro that is creating a number sequence (from 0 to 77777) only using the numbers 0 tru 7. So when I select a cel say A1 and I run the macro the first column needs to be filled with: 1, 2, 3, 4, 5, 6, 7, 10, 11, 12, 13, 14, 15, 16, 17, 20 etc etc etc. An other macro I would like to create is one that provides me with the following number sequence (lets say in column C) 0, 77, 100, 177, 200, 277, 300, 377, 400, 477, 500, 577 etc etc etc Can anybody help me with this? Regards, Paul |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating number sequence
Mike,
Thanks, can you help me with my other probleem, named: value in column. It's 3 posts above this one. Cheers, Paul "Mike H" wrote: Your welcome, it was an interesting one to have a got at "Paul Tikken" wrote: Mike, It works great, thank you very much!!! Paul "Mike H" wrote: OOPS, Formatting error Sub dec2oct() q = 1 a = 0 For b = 65 To 90 For c = 65 To 90 For x = 1 To Oct(14) Step 2 Cells(q, 3).Value = Chr(b) & Chr(c) & Format(Oct(a), "000") Cells(q + 1, 3).Value = Chr(b) & Chr(c) & Format(Oct(a + 63), "000") a = a + 64 q = q + 2 Next a = 0 Next Next End Sub Mike "Mike H" wrote: Possibly, Sub dec2oct() q = 1 a = 0 For b = 65 To 90 For c = 65 To 90 For x = 1 To Oct(14) Step 2 Cells(q, 3).Value = Chr(b) & Chr(c) & Oct(a) Cells(q + 1, 3).Value = Chr(b) & Chr(c) & Oct(a + 63) a = a + 64 q = q + 2 Next a = 0 Next Next End Sub Mike "Paul Tikken" wrote: Mike, Works like a charm!!! Thank you very much!! Mike, you might help me with another sequence. This is a combination of Alphanumeric and numeric characters. The sequence starts with; AA000, AA077, AA100, AA177 etc etc up to AA777 after this the sequence needs to proceed with AB000, AB077, AB100, AB177 up to AB777 then proceding with AC000 etc etc etc all the way up to AZ777 after this the sequence needs to proceed with BA000, BA077, etc etc the sequence needs to work his way up to ZZ777. All alphanumeric characters (A-Z) can be used and only numeric characters 0 tru 7. This can be a seperate macro for column E. Thanks again!! Paul "Mike H" wrote: Paul, Another attempt at Octal Sub dec2oct() x = 1 Do Cells(x, 1).Value = Oct(x) x = x + 1 Loop Until Cells(x - 1, 1).Value = 777 y = 1 a = 0 Do Cells(y, 3).Value = Oct(a) Cells(y + 1, 3).Value = Oct(a + 63) y = y + 2 a = a + 64 Loop Until Cells(y, 1).Value = "" End Sub Mike "Paul Tikken" wrote: Gord, Thanks, but the macro Mike gave me is working fine for the first scenario, but the second scenario I forgot to mention that this sequence only can contain the digits 0 tru 7. So the sequence will be; 0, 77, 100, 177, 200, 277, 300, 377, 400, 477, 500, 577, 600, 677, 700, 777, 1000, 1077, 1100, 1177 etc etc etc Any clue? Paul "Gord Dibben" wrote: First macro..................... Sub Fill_Nums() With Range("A1") .FormulaR1C1 = "1" .Select Selection.AutoFill Destination:=Range("A1:A65536"), _ Type:=xlFillSeries End With End Sub Cannot provide a macro for second scenario. I do not understand the sequence. Gord Dibben MS Excel MVP On Thu, 31 Jan 2008 11:41:05 -0800, Paul Tikken wrote: I'm using excel 2003 I would like to create a macro that is creating a number sequence (from 0 to 77777) only using the numbers 0 tru 7. So when I select a cel say A1 and I run the macro the first column needs to be filled with: 1, 2, 3, 4, 5, 6, 7, 10, 11, 12, 13, 14, 15, 16, 17, 20 etc etc etc. An other macro I would like to create is one that provides me with the following number sequence (lets say in column C) 0, 77, 100, 177, 200, 277, 300, 377, 400, 477, 500, 577 etc etc etc Can anybody help me with this? Regards, Paul |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looking for next number in sequence | Excel Discussion (Misc queries) | |||
Row Number out of Sequence | Excel Discussion (Misc queries) | |||
Creating a list of numbers in sequence | Excel Worksheet Functions | |||
How can I match a random number with closest number from sequence? | Excel Worksheet Functions | |||
number sequence gap | Excel Worksheet Functions |