Counting Octals.

[Paul Tikken]

Hi,

I'm using a sheet that has Octal values in it, so only 0 tru 7 are used as
numbers.

A value in column F in entered in Octals e.g. 00200, another value is
entered in column G e.g. 00377.
Now I would like the total amount of numbers to show up in Column H, so in
this case 128.

There are a couple of snacks, the following numbers cannot be used and
therefore should not be counted in the total amount; 77 and 176 and 177 and
77777 (77777 is also the last number in the sequence.

Can anybode help?

Paul.

 

[Mike H]

Paul,

Still doing Octal I see. I think we need some clarification
200+377 octal=577 octal
200+377 octal = 383 decimal

As you will note, neither are the 128 you want
(128 oct = decimal 200)
DEC2OCT and OCT2DEC are something to look at

Mike

 

[Paul Tikken]

Mike,

Yes still going strong on octals.

My bad, let me clearify;

The 200 and 377 I spoke about is an allocated range, meaning that the number
200 tru 277 and 300 tru 377 can be used, but only the numbers 0 tru 7 (so
that's why I spoke about octals) if you add these up you'll get 128 (64 for
200 tru 277 and 64 for 300 tru 377)

Any ideas?

Paul

 

[Joel]

Sub fromoctal()
TextNum = Trim(Range("A1").Text)
OctNum = 0
Do While Len(TextNum) > 0
OctNum = (8 * OctNum) + Val(Left(TextNum, 1))
TextNum = Mid(TextNum, 2)
Loop
End Sub

 

[Paul Tikken]

Joel,

I couldn't get it to work.

Do I need to tweak it anywhere?

Paul

 

[Joel]

The code just performs a conversion from octal to decimal. I used as ax
example getting data from cell A1 (TextNum = Trim(Range("A1").Text). the
code could be writen as a function like the new code below

cal with =fromoctal(A1) from worksheet

Function fromoctal(Target as Range)
TextNum = Target.Text
DecNum = 0
Do While Len(TextNum) > 0
DecNum = (8 * DecNum) + Val(Left(TextNum, 1))
TextNum = Mid(TextNum, 2)
Loop
fromoctal = DecNum
End Function

 

[Paul Tikken]

Joel,

cal with =fromoctal(A1) from worksheet , this part of the code turns up red.

Where do I need to paste the code? In the worksheet or as a separate module?

The values are in Column F and G and the result should be in column H,
measuring the amount of numbers (0 tru 7) between the entry in column F and
the entry in column G. And this for every row, so the result of F1 and G1 are
in H1, F2 and G2 are in H2 etc etc.

any ideas?

Paul

 

[Joel]

Let use a function with two input parameters. Put the code in a VBA module.

Put in column H1. then copy down column.
=addoctal(F1,G1)


Function addoctal(Target1 as Range, Target2 as Range)
TextNum = Target1.Text
DecNum = 0
Do While Len(TextNum) > 0
DecNum = (8 * DecNum) + Val(Left(TextNum, 1))
TextNum = Mid(TextNum, 2)
Loop
addoctal = DecNum

TextNum = Target2.Text
DecNum = 0
Do While Len(TextNum) > 0
DecNum = (8 * DecNum) + Val(Left(TextNum, 1))
TextNum = Mid(TextNum, 2)
Loop

addoctal = addoctal + DecNum

End Function

 

[Paul Tikken]

Joel,

Thanks, I've got it working!!

Cheers,

Paul