Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with Comparing values and retrieving values in Excel!!!!!! | Excel Worksheet Functions | |||
Comparing values between columns only when there are values in bot | Excel Worksheet Functions | |||
Comparing values in two columns and displaying missing values in n | Excel Programming | |||
Fill values into a listbox matching selected values from a combobox | Excel Programming | |||
ComboBox Values | Excel Programming |