Comparing against values in a combobox??
 

I have set an initial item to a combo box. I am now trying to go through an
array to see if the values of the array are already in the combobox. If the
value is not part of the combo box, I want it to be added. Otherwise, I want
the next array value to be checked.

What is the correct syntax for addressing the combobox values?

' Establish array range
For i = 1 To count
iArray(i) = rng.Cells(i).Value
Next i

' Estabish initial member of combobox
Set box1 = UserForm1.ComboBox1
box1.Clear
box1.AddItem iArray(1)

' Compare values
For i = 1 To count
If iArray(i) < item.box1.Value Then
box1.AddItem iArray(i)

End If
Next i

Comparing against values in a combobox??
 

This might help you get going...

Private Sub CommandButton1_Click()
Dim Cell As Range
Dim CellVal As Variant
ComboBox1.Clear
For Each Cell In Range("A1:A5")
CellVal = Cell.Value
If MatchToList(CellVal) = False Then
ComboBox1.AddItem CellVal
End If
Next
End Sub

Function MatchToList(MatchItem As Variant) As Boolean
Dim Counter As Integer
For Counter = 0 To ComboBox1.ListCount - 1
If MatchItem = ComboBox1.List(Counter) Then
MatchToList = True
Exit Function
End If
Next
End Function

--
Jim
"Mark" wrote in message
...
|I have set an initial item to a combo box. I am now trying to go through an
| array to see if the values of the array are already in the combobox. If
the
| value is not part of the combo box, I want it to be added. Otherwise, I
want
| the next array value to be checked.
|
| What is the correct syntax for addressing the combobox values?
|
| ' Establish array range
| For i = 1 To count
| iArray(i) = rng.Cells(i).Value
| Next i
|
| ' Estabish initial member of combobox
| Set box1 = UserForm1.ComboBox1
| box1.Clear
| box1.AddItem iArray(1)
|
| ' Compare values
| For i = 1 To count
| If iArray(i) < item.box1.Value Then
| box1.AddItem iArray(i)
|
| End If
| Next i
|

Comparing against values in a combobox??
 

Hi Mark,
i have here a fucntion that compare 2 arrays and returns the difference. It
could be used as:
'----------------------------------------------------
Sub Test()
'add to Box1
' Note that while Range("b2:b7") returns a range,
' Range("b2:b7").Value returns an array
AddMissingItems Box1, Range("b2:b7").Value
End Sub
'----------------------------------------------------
'note: Arr = array
Private Sub AddMissingItems(cbx As MSForms.ComboBox, Arr As Variant)

Dim v, vItem, Result
Dim i As Long

With cbx

'extract column 0 into array
ReDim v(0 To .ListCount - 1)
For i = 0 To .ListCount - 1
v(i) = .List(i, 0)
Next

'Get difference
Result = ArrayDiff(Arr, v)

'add the missing items to combobox
For Each vItem In Result
.AddItem vItem
Next

End With

End Sub

'----------------------------------------------------------
'Note that in the code, if you wanted to return similar items instead of the
'difference, you would just need to replace the 'If Not Found' by 'If found'
Function ArrayDiff(ByVal Arr1 As Variant, ByVal Arr2 As Variant) As Variant
'returns array of values from Arr1 not in Arr2
Dim v, v1, v2, v3
Dim i As Long, found As Long

i = -1
With Application.WorksheetFunction
On Error Resume Next 'error if MATCH() doesn't find item
For Each v1 In Arr1
v3 = .Match(v1, Arr2, 0)
found = (Err = 0)
Err.Clear
If Not found Then '-------- If not found, then add it ---------
i = i + 1
If i = 0 Then
ReDim v(0)
v(0) = v1
Else
ReDim Preserve v(0 To i)
v(i) = v1
End If
End If
Next
End With
ArrayDiff = v
End Function
'-------------------------------------------------------
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Mark" wrote:

I have set an initial item to a combo box. I am now trying to go through an
array to see if the values of the array are already in the combobox. If the
value is not part of the combo box, I want it to be added. Otherwise, I want
the next array value to be checked.

What is the correct syntax for addressing the combobox values?

' Establish array range
For i = 1 To count
iArray(i) = rng.Cells(i).Value
Next i

' Estabish initial member of combobox
Set box1 = UserForm1.ComboBox1
box1.Clear
box1.AddItem iArray(1)

' Compare values
For i = 1 To count
If iArray(i) < item.box1.Value Then
box1.AddItem iArray(i)

End If
Next i