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

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

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

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

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

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
<(E-Mail Removed)> 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

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
> <(E-Mail Removed)> 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
>
>

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
> > <(E-Mail Removed)> 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
> >
> >

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
> > > <(E-Mail Removed)> 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
> > >
> > >

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
> > > <(E-Mail Removed)> 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
> > >
> > >