Sort Data by numbers

[alrandal]

ok Ive got a table full of 10 digit numbers "111-2222-333"

I have all these numbers in a list box on my main form, so the user can choose. Lets say the user wants to enter new data with a new number, but there are hundreds of these numbers and they dont want to have to scroll through to see the next available number in sequential order, they just want to click a command button and the new number will be generated for them. Could anyone help me with this code

 

[RobBroekhuis]

try something like:

sub btnGetNewNumber_click()
dim i as integer, N as integer, Nfound as boolean
N=val(lstNumbers.list(0))
Nfound=false
i=1
do while i<ubound(lstNumbers.list) and not Nfound
if val(lstNumbers.list(i))-N>1 then
N = N+1
Nfound=true
else
N=val(lstNumbers.list(i))
end if
i=i+1
loop
N=iif(Nfound,N,N+1)
txtNewNumber=format(N)
end sub



Rob

 

[RobBroekhuis]

Two comments on my code above - first of all, the first condition in the do statement should be

i<=ubound(lstNumbers.list)

second, my code works just for normal numbers. You'd have to have your own logic to find the &quot;next&quot; allowable 10-digit sequence.
Rob

 

[alrandal]

&quot;lstNumbers&quot; what does this variable represent? Is that referencing the list box?

 

[RobBroekhuis]

Yes - lstNumbers is the listbox, btnGetNewNumber your button, and txtNewNumber a textbox that will display your new number.
Rob

 

[alrandal]

ok Im getting some errors and I ve got some questions, I changed &quot;lstNumbers&quot; to the name of my list, but what is the rest of the code .list(0)) and .list(i))?

 

[RobBroekhuis]

&quot;list&quot; is the property of the listbox that holds the items within the listbox. &quot;list(i)&quot; is the i'th element within the listbox - actually, the (i+1)th, since the list starts with item list(0).
What is the error you're getting?
Rob

 

[alrandal]

the errors are on .list Err=Data member not found and on ubound I get Err= expected erray

 

[alrandal]

the errors are on &quot;.list&quot; Err= &quot;Data member not found&quot; and on &quot;i<ubound&quot; I get Err= &quot;expected erray&quot; this is after I change the code to match my form.

 

[RobBroekhuis]

Is your code on the code sheet that belongs to the userform? I ran the code I posted without a problem. If the errors persist, post what you've got so far and I'll help you along.
Rob

 

[alrandal]

Ok this is what Ive got so far:

Private Sub btnGetNewNumber_Click()
Dim i As Integer, N As Integer, Nfound As Boolean
N = Val(List0.List(0))
Nfound = False
i = 1
Do While i <= UBound(List0.List) And Not Nfound
If Val(List0.List(i)) - N > 1 Then
N = N + 1
Nfound = True
Else
N = Val(List0.List(i))
End If
i = i + 1
Loop
N = IIf(Nfound, N, N + 1)
txtNewNumber = Format(N)

End Sub

I made the button name the same, and I created the text box named txtNewNumber and I changed the name of &quot;lstNumbers&quot; to &quot;List0&quot;, List0 is the name of my List. When I debug on this code I get &quot;Method or Data member not found on .list in (List0.List(0)) and on UBound in &quot;<= UBound&quot;. Thanks for all your help Rob

 

[RobBroekhuis]

I don't know why it wouldn't work. Can you run up to a breakpoint in your code, and (in the debug window) type
?typename(<your userform name>.list0)
The typename should be ListBox, if it's not there's a problem with the way your userform controls are set up.

Rob