On Apr 25, 9:48*am, Danny wrote:
On Apr 24, 10:21*am, "Nigel" wrote:





The issue is that your database will contain duplicates and your original OP
said the value in cell A1 suggested a single value not a list.

So if the list is in column A (sheet1) then use this code to populate the
list box.....

Sub CCList()
* * Dim AllCells As Range, Cell As Range
* * Dim NoDupes As New Collection
* * Dim i As Integer, j As Integer
* * Dim Swap1, Swap2, Item

' * The items are in A1 to last row in column A
* * With Sheets(1)
* * * Set AllCells = .Range("A2:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
* * End With

' * The next statement ignores the error caused
' * by attempting to add a duplicate key to the collection.
' * The duplicate is not added - which is just what we want!

* * On Error Resume Next
* * For Each Cell In AllCells
* * * * If Not Cell.EntireRow.Hidden Then
* * * * NoDupes.Add Cell.Value, CStr(Cell.Value)
* * * * End If
* * Next Cell

' * Resume normal error handling
* * On Error GoTo 0

' * Sort the collection (optional)
* * For i = 1 To NoDupes.Count - 1
* * * * For j = i + 1 To NoDupes.Count
* * * * * * If NoDupes(i) NoDupes(j) Then
* * * * * * * * Swap1 = NoDupes(i)
* * * * * * * * Swap2 = NoDupes(j)
* * * * * * * * NoDupes.Add Swap1, befo=j
* * * * * * * * NoDupes.Add Swap2, befo=i
* * * * * * * * NoDupes.Remove i + 1
* * * * * * * * NoDupes.Remove j + 1
* * * * * * End If
* * * * Next j
* * Next i

' * Add the sorted, non-duplicated items to a ListBox
* * With Sheets(1).ListBox1
* * *.Clear
* * *For Each Item In NoDupes
* * * *.AddItem Item
* * *Next Item
* * End With
End Sub

--

Regards,
Nigel


"Danny" wrote in message

...
On Apr 23, 11:08 am, "Nigel" wrote:

If you have hundreds of CCs and you wish to ListBox them, where is the
list
stored?

--

Regards,
Nigel


"Danny" wrote in message

....

I would like to find out if it is possible in Excel 2003 to build a
list box that does the following

If a field Cell A1 is populated with cc=23456 or cc=12345 or cc=45678
(CC is Cost Center)
Is it possible to get this into a list box using vba in this format

cc=23456
cc=12345
cc=45678

If so how?

The second issue is the field Cell A1 that the list box pulls from can
be populated with different numbers of costcenters and values
example
cc=23456 or cc=12345 or cc=45678 or cc67890 Which is 4 different cc's
the above example only has 3. There could be 100 cc's

The logic is always the same format cc=5 digits or cc=5digits and has
or seperating them etc...

Is it possible to use VBA to list into a listbox the above scenarios?

If you need more information please let me know.

Thanks- Hide quoted text -

- Show quoted text -

The information is stored in the format above in a database. *I am
extracting the data into Excel. *Why? *Is it not possible to write vba
with the logic above doing a loop and adding the results to a listbox?- Hide quoted text -

- Show quoted text -

Nigel,

I really appreciate your response. *Forgive me but I am not sure I
follow the code above. I tried it out and it doesn't seem to fit what
I am trying to do.

I may have confused the issue. *In Cell A1 the data that is populated
into A1 and only cell A1 as CC=12345 Or CC=34567 this information is
dynamic and changes in length A1 could be CC=12345 or CC=34567 or
CC=23456

Example 1.
so cell A1 could have
CC=12345 or CC=34567 in one instance

Example 2
In another instance cell A1 could have
CC=12345 or CC=34567 or CC=23456
So what I want to do is list in a listbox the following information

From Example 1 above
CC=12345
CC=34567

From Example 2
CC=12345
CC=34567
CC=23456

I hope this makes sense. *It is kind of tought to follow. *But the
database can have all of these values in one field for that 1 record
and that is what the query retrieves.

Thanks again for your response.- Hide quoted text -

- Show quoted text -

I may have been going about this all wrong. I relooked at what I was
trying to do. Maybe text to columns would work better. So if A1 has
CC=12345 or CC=34567 it would parse it out to be
A1 value is 12345
B1 Value is or
C1 value is 34567.

Or if A1 has CC=12345 or CC=34567 or CC=27689

Then it would parse out to
A1 Value is 12345
B1 Value is or
C1 value is 34567
D1 value is or
E1 value is 27689

1. With this in mind is there away to loop in a text to columns to
parse it out until there isn't a value to parse out in Field A1?
2. Then take all of those fields that are populated and select those
fields that are populated with values to go into a list box?

Thanks
Gosh I hope this makes sense :)