creating number sequence

[Paul Tikken]

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 H]

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]

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]

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]

Tanks Mike, appreciate it!

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

 

[Gord Dibben]

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

 

[Paul Tikken]

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

 

[Mike H]

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

 

[Mike H]

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

 

[Paul Tikken]

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]

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

 

[Mike H]

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

 

[Paul Tikken]

Mike,

It works great, thank you very much!!!

Paul

 

[Mike H]

Your welcome, it was an interesting one to have a got at

 

[Paul Tikken]

Mike,

Thanks, can you help me with my other probleem, named: value in column. It's
3 posts above this one.

Cheers,

Paul