3 possible methods for adding value to a combobox
 

I have values in some cells in Range A1:A100. What is the code I would use
to add each value to combobox1 only if that value isnt already located above
where it currently is in the loop.

For instance,
Lets say the first 5 values it comes across are all different, then it hits
the 6th value. Lets say the 3rd value matches the 6th, since that value is
already in the list, then the code either skips the 6th value, OR adds and
then removes it, OR removes the 3rd value and adds the 6th. No matter which
of the 3 logics you use, it will still prevent duplicate values from being
added to the combobox.

Is this a possible method or no?


Thanx

Todd Huttenstine

3 possible methods for adding value to a combobox
 

for each cell in Range("A1:A100")
if cell.row < 1 then
res =
application.Match(cell.Value,Range(Range("A1"),Cel l.offset(-1,0)),0)
if iserror(res) then
combobox1.AddItem cell.Value
end if
else
combobox1.AddItem Cell.Value
end if
Next

--
Regards,
Tom Ogilvy


Todd Huttenstine wrote in message
...
I have values in some cells in Range A1:A100. What is the code I would
use
to add each value to combobox1 only if that value isnt already located
above
where it currently is in the loop.

For instance,
Lets say the first 5 values it comes across are all different, then it
hits
the 6th value. Lets say the 3rd value matches the 6th, since that value
is
already in the list, then the code either skips the 6th value, OR adds and
then removes it, OR removes the 3rd value and adds the 6th. No matter
which
of the 3 logics you use, it will still prevent duplicate values from being
added to the combobox.

Is this a possible method or no?


Thanx

Todd Huttenstine

3 possible methods for adding value to a combobox
 

Hi Todd,

Here's one way

Dim i As Long

With ActiveSheet.ComboBox1
.Clear
For i = 1 To 100
If WorksheetFunction.CountIf(Range("A1", Cells(i, "A")),
Cells(i, "A")) = 1 Then
.AddItem Cells(i, "A").Value
End If
Next i
End With


--

HTH

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

"Todd Huttenstine" wrote in message
...
I have values in some cells in Range A1:A100. What is the code I would
use
to add each value to combobox1 only if that value isnt already located
above
where it currently is in the loop.

For instance,
Lets say the first 5 values it comes across are all different, then it
hits
the 6th value. Lets say the 3rd value matches the 6th, since that value
is
already in the list, then the code either skips the 6th value, OR adds and
then removes it, OR removes the 3rd value and adds the 6th. No matter
which
of the 3 logics you use, it will still prevent duplicate values from being
added to the combobox.

Is this a possible method or no?


Thanx

Todd Huttenstine

3 possible methods for adding value to a combobox
 

Thank you Tom.


"Tom Ogilvy" wrote in message
...
for each cell in Range("A1:A100")
if cell.row < 1 then
res =
application.Match(cell.Value,Range(Range("A1"),Cel l.offset(-1,0)),0)
if iserror(res) then
combobox1.AddItem cell.Value
end if
else
combobox1.AddItem Cell.Value
end if
Next

--
Regards,
Tom Ogilvy


Todd Huttenstine wrote in message
...
I have values in some cells in Range A1:A100. What is the code I would
use
to add each value to combobox1 only if that value isnt already located
above
where it currently is in the loop.

For instance,
Lets say the first 5 values it comes across are all different, then it
hits
the 6th value. Lets say the 3rd value matches the 6th, since that value
is
already in the list, then the code either skips the 6th value, OR adds
and
then removes it, OR removes the 3rd value and adds the 6th. No matter
which
of the 3 logics you use, it will still prevent duplicate values from
being
added to the combobox.

Is this a possible method or no?


Thanx

Todd Huttenstine