DaveGib
02-15-2014, 07:08 AM
Hi All,
I have a problem that has got me beat, and I am hoping someone can please help:help
What I am trying to do - is create a 2nd validation list, dependant on the result of the 1st validation list, which puts the result in a formula to look up a particular range item and get its details.
What I have - are several sheets (15 actually) that each contain information about a particular plant range ( machinery not botanical). I have named each sheet by the prefix of each plant range, i.e. - sheet ("BL") contains the information regarding the plant items that begin with BL - i.e. BL101, BL102, .... BL120etc.
In each sheet I have created a Table with the same name as its prefix, i.e. "Tbl_BL", with a named range called R_BL, which is the lookup range I use to find the item detail.
I can get this all to work with static information but not using a variable.
Here is what I have....
Dave
' This code creates the first validation list and works ok
Range("D10").Select
With Selection.Validation ' Create Validation List for Plant Prefix
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=PlantPrefix" ' PlantPrefix is a named range of all the prefixes
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
' **** This is where I have a problem....
Dim p As Variant ' I have tried String as well, I have also tried Range but do not know what 'Range' I should Set
If Range("D10").Value = "BL" Then
p = "BL"
ElseIf Range("D10").Value = "BR" Then
p = "BR"
ElseIf Range("D10").Value = "CS" Then
p = "CS"
' ElseIf Range("D10").Value = "DO" Then '.... etc
' p = "DO"
End If
' If I put a watch on p I get whatever is selected in D10 in quotations e.g. "BL"
' The line below that I have commented out works ok, but the line above it
' where I try to use the variable does not
Range("K10").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=IF(D10=""p"",R_" & p & ","""")" ' <=========HERE
' xlBetween, Formula1:="=IF(D10=""CS"",R_CS,"""")"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
' *** and in case it is of any interest to someone else here is the code that I use
' to look up the detail, - which all works ok
Dim SearchRange As Range
Dim Make As Variant
Dim Model As Variant
Dim Descripton As Variant
Dim ChassisNumber As Variant
Dim EngineNumber As Variant
Dim VinNumber As Variant
Dim PlantNumber As Variant
Dim Prefix As String
Dim PlantList As Range
Prefix = Cells(10, 4).Value ' Plant Prefix in Validation List in D10
Sheets(Prefix).Select
'Sheets("CS").Select
Set PlantList = Sheets(Prefix).Range("R_" & Prefix & "")
Set SearchRange = Sheets(Prefix).Range("Tbl_" & Prefix & "")
' Set SearchRange = Sheets("CS").Range("A1:G4")
Sheets("Requisition").Select
Range("K10").Select ' Create Validation List for Plant Number
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=PlantList"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
PlantNumber = Cells(10, 11).Value ' Plant Number in Validation list in K10
Make = Application.WorksheetFunction.VLookup(PlantNumber, SearchRange, 2, False)
Model = Application.WorksheetFunction.VLookup(PlantNumber, SearchRange, 3, False)
Description = Application.WorksheetFunction.VLookup(PlantNumber, SearchRange, 4, False)
ChassisNumber = Application.WorksheetFunction.VLookup(PlantNumber, SearchRange, 5, False)
EngineNumber = Application.WorksheetFunction.VLookup(PlantNumber, SearchRange, 6, False)
VinNumber = Application.WorksheetFunction.VLookup(PlantNumber, SearchRange, 7, False)
' I then place the information in a sheet......
Sheets("Requisition").Select
Range("C12").Value = Description
Range("C13").Value = Make
Range("C14").Value = Model
Range("L12").Value = ChassisNumber
Range("L13").Value = EngineNumber
Range("L14").Value = VinNumber
I have a problem that has got me beat, and I am hoping someone can please help:help
What I am trying to do - is create a 2nd validation list, dependant on the result of the 1st validation list, which puts the result in a formula to look up a particular range item and get its details.
What I have - are several sheets (15 actually) that each contain information about a particular plant range ( machinery not botanical). I have named each sheet by the prefix of each plant range, i.e. - sheet ("BL") contains the information regarding the plant items that begin with BL - i.e. BL101, BL102, .... BL120etc.
In each sheet I have created a Table with the same name as its prefix, i.e. "Tbl_BL", with a named range called R_BL, which is the lookup range I use to find the item detail.
I can get this all to work with static information but not using a variable.
Here is what I have....
Dave
' This code creates the first validation list and works ok
Range("D10").Select
With Selection.Validation ' Create Validation List for Plant Prefix
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=PlantPrefix" ' PlantPrefix is a named range of all the prefixes
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
' **** This is where I have a problem....
Dim p As Variant ' I have tried String as well, I have also tried Range but do not know what 'Range' I should Set
If Range("D10").Value = "BL" Then
p = "BL"
ElseIf Range("D10").Value = "BR" Then
p = "BR"
ElseIf Range("D10").Value = "CS" Then
p = "CS"
' ElseIf Range("D10").Value = "DO" Then '.... etc
' p = "DO"
End If
' If I put a watch on p I get whatever is selected in D10 in quotations e.g. "BL"
' The line below that I have commented out works ok, but the line above it
' where I try to use the variable does not
Range("K10").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=IF(D10=""p"",R_" & p & ","""")" ' <=========HERE
' xlBetween, Formula1:="=IF(D10=""CS"",R_CS,"""")"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
' *** and in case it is of any interest to someone else here is the code that I use
' to look up the detail, - which all works ok
Dim SearchRange As Range
Dim Make As Variant
Dim Model As Variant
Dim Descripton As Variant
Dim ChassisNumber As Variant
Dim EngineNumber As Variant
Dim VinNumber As Variant
Dim PlantNumber As Variant
Dim Prefix As String
Dim PlantList As Range
Prefix = Cells(10, 4).Value ' Plant Prefix in Validation List in D10
Sheets(Prefix).Select
'Sheets("CS").Select
Set PlantList = Sheets(Prefix).Range("R_" & Prefix & "")
Set SearchRange = Sheets(Prefix).Range("Tbl_" & Prefix & "")
' Set SearchRange = Sheets("CS").Range("A1:G4")
Sheets("Requisition").Select
Range("K10").Select ' Create Validation List for Plant Number
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=PlantList"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
PlantNumber = Cells(10, 11).Value ' Plant Number in Validation list in K10
Make = Application.WorksheetFunction.VLookup(PlantNumber, SearchRange, 2, False)
Model = Application.WorksheetFunction.VLookup(PlantNumber, SearchRange, 3, False)
Description = Application.WorksheetFunction.VLookup(PlantNumber, SearchRange, 4, False)
ChassisNumber = Application.WorksheetFunction.VLookup(PlantNumber, SearchRange, 5, False)
EngineNumber = Application.WorksheetFunction.VLookup(PlantNumber, SearchRange, 6, False)
VinNumber = Application.WorksheetFunction.VLookup(PlantNumber, SearchRange, 7, False)
' I then place the information in a sheet......
Sheets("Requisition").Select
Range("C12").Value = Description
Range("C13").Value = Make
Range("C14").Value = Model
Range("L12").Value = ChassisNumber
Range("L13").Value = EngineNumber
Range("L14").Value = VinNumber