Let me consider an array of columns: Cnd1, Cnd2, Val1, Val2, Item,
Cnd3.
Cnd1..2..3 contain text and numbers for conditions.
Below Combobox macro builds a list of unique values from Item column.
I'd like to add conditions so that it adds items only if Cnd2 and Cnd3
contains a given substring stored somewhere in a sheet.
Macro GUI: UserForm1, CommandButton1 (Print button), ComboBox1 (List
of items)
--
Sub shows()
UserForm1.Show
End Sub
--
Option Explicit
Dim FArray()
Dim DataList As Range
Dim MyList As String
Private Sub UserForm_Initialize()
Dim Found As Long, i As Long
Dim cel As Range
'Set Range Name to suit
MyList = "Data"
Set DataList = Range(MyList)
ReDim FArray(DataList.Cells.Count)
i = -1
For Each cel In DataList
On Error Resume Next
Found = Application.WorksheetFunction.Match(CStr(cel), FArray,
0)
If Found > 0 Then GoTo Exists
i = i + 1
FArray(i) = Str(i) + Chr(187) + Chr(9) + cel 'PRINT ITEMS Chr
(9)
Exists:
Found = 0
Next
ReDim Preserve FArray(i)
Call BubbleSort(FArray)
ComboBox1.ListRows = 10
ComboBox1.List() = FArray
End Sub
Private Sub ComboBox1_AfterUpdate()
Dim MyAdd As String
Dim Found As Long
On Error Resume Next
Found = Application.WorksheetFunction.Match(ComboBox1, FArray, 0)
If Found > 0 Then
DoEvents
Else
DataList.End(xlDown).Offset(1) = ComboBox1
Set DataList = Union(DataList, DataList.End(xlDown))
MyAdd = "=" & ActiveSheet.Name & "!" & DataList.Address
ActiveWorkbook.Names.Add Name:=MyList, _
RefersTo:=MyAdd
End If
End Sub
Private Sub CommandButton1_Click()
Dim strTemp As String
Dim strCount As Integer
strTemp = ComboBox1
strCount = InStr(strTemp, Chr(9))
strTemp = Right(strTemp, Len(strTemp) - strCount)
Cells(Cells.Rows.Count, 4).End(xlUp).Offset(1) = strTemp
Set DataList = Nothing
Unload UserForm1
End Sub
Sub BubbleSort(MyArray As Variant)
Dim First As Integer
Dim Last As Integer
Dim i As Integer
Dim j As Integer
Dim Temp As String
Dim List As String
Dim k As Integer
First = LBound(MyArray)
Last = UBound(MyArray)
For i = First To Last - 1
For j = i + 1 To Last
If MyArray(i) > MyArray(j) Then
Temp = MyArray(j)
MyArray(j) = MyArray(i)
'k = InStr(Temp, Chr(9))
MyArray(i) = Temp 'Right(Temp, Len(Temp) - k)
End If
Next j
Next i
End Sub
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "MS
Excel & VBA Macros" group.
To post to this group, send email to excel-macros@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/excel-macros?hl=en
-~----------~----~----~----~------~----~------~--~---
