Data Validation List based on IF Statement

[PVA0724]

Hi Members,

I'm trying to pull out a validation list based on a if statement, with the following code

Sub ValJBA()
Dim i As Long
Dim sList As String
Dim dv As Validation
Dim wks As Worksheet
Set wks = Sheets("JBA - SpaEn")

    If wks.Range("C23") = "Intercompany" Then
        sList = Range("INT")
        ElseIf wks.Range("C23") = "Operative" Then
            sList = Range("CAR")
            Else
                sList = Range("CTE")
            End If
   Set dv = Range("G23").Validation
   dv.Delete
   dv.Add Type:=xlValidateList, _
   AlertStyle:=xlValidAlertStop, _
   Operator:=xlBetween, _
   Formula1:=sList
End Sub

but I'm getting a type mismatch error driven by the "sList" so I tried with

Dim sList As Range

due to the named range, but then I got an error on the following line (object variable not set - error 91)

sList = Range ("INT")

as I'm still learning macros in excel, I don't know what I'm doing wrong, so any help will be more than welcome.

 

[mikerickson]

If sList is a Range, you have to use the keyword Set when assigning a value to it. You also need a cell address when assigning the .Formula1 property of data validation.

Dim sList As Range

    If wks.Range("C23") = "Intercompany" Then
        Set sList = Range("INT")
    ElseIf wks.Range("C23") = "Operative" Then
        Set sList = Range("CAR")
    Else
        Set sList = Range("CTE")
    End If

    Set dv = Range("G23").Validation
    dv.Delete
    dv.Add Type:=xlValidateList, _
        AlertStyle:=xlValidAlertStop, _
        Operator:=xlBetween, _
        Formula1:=[COLOR="#FF0000"]"=" & sList.Address[/COLOR]

Another approach would use the names of your named ranges directly when setting the data validation

Dim rangeName as String

If wks.Range("C23").Value = "Intercompany" Then
    rangeName = "INT"
ElseIf wks.Range("C23").Value = "Operative" Then
    rangeName = "CAR"
Else
    rangeName = "CTE"
End If

With Range("G23").Validation
    .Delete

    .Add Type:=xlValidateList, _
        AlertStyle:=xlValidAlertStop, _
        Operator:=xlBetween, _
        Formula1:=[COLOR="#FF0000"]"=" & rangeName[/COLOR]
End With

An even slicker approach would be to use dependent named range and avoid VBA altogether.
Define a Name
Name: myList RefersTo: =IF(Sheet1!$C$23="Intercompany", INT, IF(Sheet1!$C$23="Operative", CAR, CTE))

Then put list style Data Validation on a cell with Source =myList and the values in the validation drop down will change as C23 changes.

 

[PVA0724]

Thank so Much, it worked. I appreciate your quick assistance.