Numbers to Lexicographic Index Number (CSN)
Thanks for the reply p45cal,
I really wanted the seperate sub which is great.
I put in numbers 44 45 46 47 48 49 and it took ages to calculate
13983816.
Is it quicker than calling the function from the sub, if so, how do I
do I call the function please?.
Thanks in Advance.
All the Best.
Paul
On Oct 3, 2:49 pm, p45cal wrote:
a small mistake; I said:
use in a spreadsheet in any cell thus:
NumbersToLex()
it should have been:
use in a spreadsheet in any cell thus:
=NumbersToLex()
---
p45cal
"p45cal" wrote:
Function NumbersToLex()
A = IIf(44 - Range("B1").Value 0, _
Application.Combin(49 - _
Range("O14").Value, 6), 0)
B = IIf(45 - Range("C1").Value 0, _
Application.Combin(49 - _
Range("P14").Value, 5), 0)
C = IIf(46 - Range("D1").Value 0, _
Application.Combin(49 - _
Range("Q14").Value, 4), 0)
D = IIf(47 - Range("E1").Value 0, _
Application.Combin(49 - _
Range("R14").Value, 3), 0)
E = IIf(48 - Range("F1").Value 0, _
Application.Combin(49 - _
Range("S14").Value, 2), 0)
F = IIf(49 - Range("G1").Value 0, _
Application.Combin(49 - _
Range("T14").Value, 1), 0)
NumbersToLex = Application.Combin(49, 6) _
- A - B - C - D - E - F
End Function
seems to do it, use in a spreadsheet in any cell thus:
NumbersToLex()
I suspect the numbers will have to be in ascending order first though.
--
p45cal
ps. another longwinded way is to make small adjustments to your code:
Sub NumbersToLex2()
nLex = 0
For A = 1 To 44
For B = A + 1 To 45
For C = B + 1 To 46
For D = C + 1 To 47
For E = D + 1 To 48
For F = E + 1 To 49
nLex = nLex + 1
If Range("B1").Value = A And Range("C1").Value = B And Range("D1").Value = C
And Range("E1").Value = D And Range("F1").Value = E And Range("G1").Value = F
Then
nVal = nLex
Range("H1") = nVal
Exit Sub
End If
Next F
Next E
Next D
Next C
Next B
Next A
End Sub
I say long winded 'cos it could take a llllooonnnggg time.
p45cal
______
"Paul Black" wrote:
Hi everyone,
I have the code ...
Option Explicit
Dim A As Integer, B As Integer, C As Integer, D As Integer, E As
Integer, F As Integer
Dim nVal As Double, nLex As Double
Sub LexToNumbers()
nVal = Range("A1").Value
nLex = 0
For A = 1 To 44
For B = A + 1 To 45
For C = B + 1 To 46
For D = C + 1 To 47
For E = D + 1 To 48
For F = E + 1 To 49
nLex = nLex + 1
If nLex = nVal Then
Range("B1").Value = A
Range("C1").Value = B
Range("D1").Value = C
Range("E1").Value = D
Range("F1").Value = E
Range("G1").Value = F
Exit Sub
End If
Next F
Next E
Next D
Next C
Next B
Next A
End Sub
.... which calculates and produces the 6 numbers associated with the
Lexicographic Index Number entered in cell "A1".
How can I get the Lexicographic Index Number from the numbers entered
in cells "B1:G1" please.
Tom Ogilvy produced the following code which might be of help :-
Function LexNumber()
LexNumber = False
a = IIf(44 - Range("O14").Value 0, _
Application.Combin(49 - _
Range("O14").Value, 6), 0)
b = IIf(45 - Range("P14").Value 0, _
Application.Combin(49 - _
Range("P14").Value, 5), 0)
c = IIf(46 - Range("Q14").Value 0, _
Application.Combin(49 - _
Range("Q14").Value, 4), 0)
d = IIf(47 - Range("R14").Value 0, _
Application.Combin(49 - _
Range("R14").Value, 3), 0)
e = IIf(48 - Range("S14").Value 0, _
Application.Combin(49 - _
Range("S14").Value, 2), 0)
f = IIf(49 - Range("T14").Value 0, _
Application.Combin(49 - _
Range("T14").Value, 1), 0)
lNumber = Application.Combin(49, 6) _
- a - b - c - d - e - f
If lNumber 22500 And lNumber < 50000 Then
LexNumber = True
End if
End Function
Thanks in Advance.
All the Best.
Paul- Hide quoted text -
- Show quoted text -
|