Problem number One

[Robert Couchman]

Hello all,

i have a problem with my VB project...

i have a user form that should display a list of dates in
a column, dependent on the data held in column "AX" on a
spreadsheet. i have this bit of code already in my program
but...

Dim rng As Range
Dim v As Variant
Dim coll As Collection
Set coll = New Collection
Set rng = Range("AX1")
On Error Resume Next
Do Until rng.Value = ""
coll.Add rng.Text, rng.Text
Set rng = rng(2, 0)
Loop
With timetable.ComboBox1
For Each v In coll
.AddItem
Next v
.ListIndex = 0
End With
timetable.Show

....when ran, it just shows empty fields in the list!!!!

and....

also not all the records in column "AX" have dates in,
therefore it will end as soon as it hits a blanc cell.

can anyone help?? and i need it to find all the results
until it reaches my final row!

thank you,

Robert Couchman

 

[Bob Phillips]

Robert,

You are not loading the combobox, so that is why you see nothing. But why
are you loading via a collection, it seems to serve no purpose that I can
ascertain.
Try this

Dim rng As Range
Dim v As Variant
Dim coll As Collection
Dim i As Long

Set coll = New Collection
Set rng = Range("AX1")
With timetable.Combobox1
For i = 1 To Cells(Rows.Count, "AX").End(xlUp).Row
If Cells(i, "AX").Value <> "" Then
.AddItem Format(Cells(i, "AX"), "dd mmm yyyy")
End If
Next i
.ListIndex = 0
End With
timetable.Show



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Robert Couchman]

Bob,

thank you for the help!! at least now i can see my
values!!!
the only problems i have though are the following...

1) i seem to get the column heading appear in my list (not
needed!)

2) the data in the records appears more than once,
therefore i seem to get the same date appearing in my list

3) the combobox auto selects the first result (need a
blank cell)

4) the data is not in an order, it appears as it does in
my records (need a sort function)

but... the code actually works ok,

so thank you!

Robert Couchman

 

[Bob Phillips]

Robert,

Now I know why you had a collection<G>.

Try this instead

Dim v As Variant
Dim coll As Collection
Dim i As Long

Set coll = New Collection
Set rng = Range("AX2")
On Error Resume Next
For i = 1 To Cells(Rows.Count, "AX").End(xlUp).Row
If Cells(i, "AX").Value <> "" Then
coll.Add Cells(i, "AX").Text, Cells(i, "AX").Text
End If
Next i
On Error GoTo 0
With timetable.Combobox1
For Each v In coll
.AddItem v
Next v
.ListIndex = 0
End With
timetable.Show

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Robert Couchman]

Hello Bob,

***1) i seem to get the column heading appear in my list
(not needed!)***

NO LONGER A PROBLEM!!!

i noticed that i was counting from 1 where row 1 is the
column headings, therefore if the 1 was changed to a 2,
then it would count from the first data entry!

thank you,

now for the other problems!!!!

Robert Couchman

 

[Robert Couchman]

Hello Bob,

good news we have managed to illiminate 3 out of 4 of my
original problems in less than half an hour!!

**answer** set the listindex to -1 so when it opens it has
a record that is blank!!!!

Now for this final question, how do we sort the data so it
appears in the list in a logical order???

thank you,

Robert Couchman

 

[Bob Phillips]

Hi Robert,

Final problem

Dim rng As Range
Dim v As Variant
Dim coll As Collection
Dim i As Long

Set coll = New Collection
Set rng = Range("AX2")
Columns("AX:AX").Sort Key1:=Range("AX2"), _
Order1:=xlAscending, _
Header:=xlYes
On Error Resume Next
For i = 2 To Cells(Rows.Count, "AX").End(xlUp).Row
If Cells(i, "AX").Value <> "" Then
coll.Add Cells(i, "AX").Text, Cells(i, "AX").Text
End If
Next i
On Error GoTo 0
With timetable.Combobox1
For Each v In coll
.AddItem v
Next v
.ListIndex = 0
End With
timetable.Show

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Robert Couchman]

Hi Bob,

seems ok except for one slight hickup!!

if i was to sort column ("AX:AX") then it will effect all
my records!!!

is there not any logical way to sort the data held in my
collection??

just thought i would ask!!!

thank you,

Robert Couchman

 

[Bob Phillips]

Robert,

I just saw your other post and was going to add to this.

Could you not sort the whole records, that is Columns("AX:BY") or whatever
they span?

If you must sort the collection, that is a bit trickier as we are storing
date strings, so we will need to change that, and then format the date in
the combo again

Dim rng As Range
Dim v As Variant
Dim coll As Collection
Dim i As Long
Dim j As Long
Dim tmp1, tmp2

Set coll = New Collection
Set rng = Range("AX2")

On Error Resume Next
For i = 2 To Cells(Rows.Count, "AX").End(xlUp).Row
If Cells(i, "AX").Value <> "" Then
coll.Add Cells(i, "AX").Value, Cells(i, "AX").Text
End If
Next i
On Error GoTo 0

For i = 1 To coll.Count - 1
For j = i + 1 To coll.Count
If coll(i) > coll(j) Then
tmp1 = coll(i)
tmp2 = coll(j)
coll.Add tmp1, before:=j
coll.Add tmp2, before:=i
coll.Remove i + 1
coll.Remove j + 1
End If
Next j
Next i

With timetable.Combobox1
For Each v In coll
.AddItem Format(v, "dd mmm yyyy")
Next v
.ListIndex = 0
End With
timetable.Show

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

Mark this away for the future

John Walkenbach's site:

http://j-walk.com/ss/excel/tips/tip47.htm
Fill a ListBox With Unique Items

Comboxbox, listbox would be the same.