The following is what I have... what I need is someone to help me put an if statement so the list is only created if the selection in the product box is either 90005 or Trac107+...... can someoen help me put this if statement in there in the proper place....
2) Is there a way to put an if statement with the Index array formula that is in the cells? I need the tank # to be if product = trac107 or 90005 to go off of the plant location if not leave blank.... or just put the plant location from the master list....
Thanks,
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WS As Worksheet
Dim WSTest As Worksheet
Dim A As Long
Dim LastRow As Long
Dim Unique As New Collection
Dim Unique2 As New Collection
Dim ValList As String
Dim C As Range, FirstAddress As String
'Fromula needed for Customer sheet.
'=INDEX('Master List'!B3:R104, MATCH(1, ('Master List'!C3:C104=A2)*('Master List'!E3:E104=A42), 0),5)
Set WS = Worksheets("Master List")
With WS
If .AutoFilterMode = True Then
.AutoFilterMode = False
End If
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
'Generate Plant unique list.
For A = 3 To LastRow
On Error Resume Next
Unique.Add CStr(.Range("C" & A)), CStr(.Range("C" & A))
On Error GoTo 0
Next
'Generate product list per Plant name.
For A = 1 To Unique.Count
'Search master list for list of products.
With WS.Range("C2:C" & LastRow)
Set C = .Find(Unique(A), LookIn:=xlValues)
If Not C Is Nothing Then
FirstAddress = C.Address
Do
On Error Resume Next
Unique2.Add CStr(C.Offset(, 4)), CStr(C.Offset(, 4))
On Error GoTo 0
Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Address <> FirstAddress
End If
End With
'Build validation list items
For B = 1 To Unique2.Count
ValList = ValList & Unique2(B) & ","
Next
Set Unique2 = Nothing
Set Unique2 = New Collection
'Trim any spaces
ValList = Trim(ValList)
'Trim trailing comma
ValList = Left(ValList, Len(ValList) - 1)
'Test for valid sheet name
On Error Resume Next
Set WSTest = Worksheets(Unique(A))
On Error GoTo 0
If Not WSTest Is Nothing Then
Set WSTest = Nothing
'Assign Unique2 to validation list per sheet.
With Worksheets(Unique(A)).Range("C30:C50").Validation
.Delete
On Error Resume Next
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=ValList
On Error Resume Next
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = False
End With
End If
ValList = ""
Next
End With
End Sub
LinkBack URL
About LinkBacks
For Loop to Add Items to a Listbox!!!
Register To Reply
Originally Posted by cory0789
Bookmarks