Incrementing a number with non-decimal base

[Guest]

Hey guys,

I am new to all this so I hope you can help me. I need a function / macro
that will increment a number with a non-decimal base??

My base number is:0123456789BCDFGHJKLMNPQRSTVWXYZ
I an using a 4 digit number.

So for example I need to put in BCR0 and have it run an increment
BCR1
BCR2
BCR3
BCR4
BCR5
BCR6
BCR7
BCR8
BCR9
BCRB
BCRC
BCRD
BCRF
and so on.

Please let me know if and how this can be done in Excel

Many Thanks

 

[dbarelli]

Maybe this is not the best way to do this but its an idea..

Dim X as Variant

x=0
Do while condicion
if isnumeric(x) then
if x=9 then
x="a"
else
x=x+1
end if
else
if x="z" then
x=0
else
x=chr(asc(x)+1)
end if
end if
Loop

BRgds.

 

[Guest]

Patrick

As the number sequence is not linear ie there are missing letter it is a
little more difficult. A possible solution is:

Function incnum(ByVal n As String) As String
Dim seq As Variant
Dim ans As String
Dim idx As Integer
seq = Array("0", "1", "2", "3", "4", "5", "6", "7", "8", "9", _
"B", "C", "D", "F", "G", "H", "J", "K", "L", "M", _
"N", "P", "Q", "R", "S", "T", "V", "W", "X", "Y", _
"Z", "!") ' use a extra stopper !
n = UCase(Trim(n))
If (Len(n) = 0) Then ' deal with the empty string
incnum = seq(LBound(seq) + 1) ' generally adding 1 to Z
Exit Function
Else ' otherwise find the number
For idx = LBound(seq) To UBound(seq) - 1
If (Right(n, 1) = seq(idx)) Then ' deal with the max number
ans = seq(idx + 1)
If ans = seq(UBound(seq)) Then
incnum = incnum(Left(n, Len(n) - 1)) & "0" ' and then
recursively call
Else
incnum = Left(n, Len(n) - 1) & ans ' normal increment
End If
Exit Function
End If
Next idx
End If
End Function

 

[Guest]

That is fancastic, thank you Martin.

May be back with another one...