PDA

View Full Version : Set max value in array item?



Ago
04-04-2010, 09:11 AM
I dont know how to make a better title.

What i need is a way to set a max value on each item.
What im trying to do is create a function to convert numbers to text as:
001 = AAA
002 = AAB
003 = AAC
026 = ABA (<- not sure about that, but you get the picture).


'width is the number of letters.
'Files is a array of userselected files
'statnumber is what the user defines as startnumber (or letter)
Width = Fix((UBound(Files) + StartNumber) / 25) + 1

ReDim Letters(Width) As Variant


That should give me a array with the correct "width".
What i need now is to make Letters[1,1,1,25] "+1" to make Letters[1,1,2,1] is that possible?

My plan is to use ChrW(Letters(1)+65) & ChrW(Letters(2)+65) ... and so on to make the letter combination.
Or have i missed a simplers solution?

Ago
04-04-2010, 09:20 AM
Just noticed a litle misstake.
It should be ChrW(Letters(1)+64) not 65

mdmackillop
04-04-2010, 09:51 AM
Sub Test()
MsgBox Letters(InputBox("Enter number"))
End Sub

Function Letters(i As Long)
x = (i - 1) Mod 26
y = (i - 1) \ 26
Z = (i - 1) \ 26 ^ 2
L1 = Chr(65 + x)
L2 = Chr(65 + y)
L3 = Chr(65 + Z)
Letters = L3 & L2 & L1
End Function



You can check the combinations with this


Sub TestLetters()
For i = 1 To 1000
x = (i - 1) Mod 26
y = (i - 1) \ 26
Z = (i - 1) \ 26 ^ 2
L1 = Chr(65 + x)
L2 = Chr(65 + y)
L3 = Chr(65 + Z)
Cells(i, 1) = L3 & L2 & L1
Next
End Sub

Ago
04-04-2010, 10:36 AM
Thanks for the help!
There is a problem, X works, Y works but does not reset, Z stops at 1 because Y dont work.

I might have been unlear about one part.
I dont want to restrict the user to get 3 letters.
If its less than 26 only one letter should be part of the end string.
Thats the reason for:

Width = Fix((UBound(Files) + StartNumber) / 26) + 1

It basicly rounds the value up, Fix(22)/26+1 =1, Fix(27)/26+1 =2
And when i ReDim Letters array i get the the correct "width" of letters.

mdmackillop
04-04-2010, 11:15 AM
I noticed an error in my code, I'll get back to you shortly.

mdmackillop
04-04-2010, 11:51 AM
I found a solution here
http://www.freevbcode.com/ShowCode.Asp?ID=6801

Ago
04-04-2010, 12:10 PM
Good find! Thank you.

I think i can change that to what i need.