3 possible methods for adding value to a combobox

[Todd Huttenstine]

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

 

[Tom Ogilvy]

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

 

[Bob Phillips]

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]

Thank you Tom.

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