Using a Variable in creating a Validation List

[DaveGib]

Hi All,
I have a problem that has got me beat, and I am hoping someone can please 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

[mancubus]

hi.

what you need is "dependent data validation."

see: http://www.contextures.com/xlDataVal15.html

you can download the demo file at the bottom of the page.

[DaveGib]

Hi mancubus
Thanks for the very useful link, it has helped to sort out my problem.......