VBA Setting a Range Variable to a Specific Worksheet

[CPDan]

Hi all,

So I'm looking to set range variables equal to corresponding lists to create a data validation drop down.

I want the data validation cells on my homepage sheet, titled "Input Sheet" and the data lists on a hidden sheet titled "Data Validation".

Here's my current code:

Dim Rng1 as range

Sheets("Data Validation").Select
        Set Rng1 = Sheets("Data Validation").Range("A1", Range("A1").End(xlDown))
        Sheets("Input Sheet").Select
        With Range("U13").Validation
            .Delete
            .Add Type:=xlValidateList, Formula1:="=" & Rng1.Address & ""
        End With

This is just a small portion of the code, and I repeat this process for 2 other data validation lists.

The code works fine, with the exception of the range reference. It pulls the range from the input sheet instead of the data validation sheet.

Any help is much appreciated. Thanks!

 

[Jonmo1]

The two highlighted ranges below are seperate independant ranges
Set Rng1 = Sheets("Data Validation").Range("A1", Range("A1").End(xlDown))
The sheet must be specified on BOTH.

Try

With Sheets("Data Validation")
    Set Rng1 = .Range("A1", .Range("A1").End(xlDown))
End With

 

[CPDan]

Hi Jonmo,

Thanks for the quick reply. I've incorporated what I think you're saying into my code, but I'm still running into the same problem

'Setting ranges
    Sheets("Data Validation").Select
    With Sheets("Data Validation")
        Set Rng1 = .Range("A1", .Range("A1").End(xlDown))
        Set Rng2 = .Range("B1", .Range("B1").End(xlDown))
        Set Rng3 = .Range("C1", .Range("C1").End(xlDown))
    End With
    
        'Region input
        Sheets("Input Sheet").Select
        With Range("C13").Validation
            .Delete
            .Add Type:=xlValidateList, Formula1:="=" & Rng3.Address & ""
        End With
    
        'Capability input
        With Range("U13").Validation
            .Delete
            .Add Type:=xlValidateList, Formula1:="=" & Rng1.Address & ""
        End With
        
        'Certification input
        With Range("L13").Validation
            .Delete
            .Add Type:=xlValidateList, Formula1:="=" & Rng2.Address & ""
        End With

 

[Norie]

Try this.

Dim Rng1 As Range
Dim Rng2 As Range
Dim Rng3 As Range

    With Sheets("Data Validation")
        Set Rng1 = .Range("A1", .Range("A1").End(xlDown))
        Set Rng2 = .Range("B1", .Range("B1").End(xlDown))
        Set Rng3 = .Range("C1", .Range("C1").End(xlDown))
    End With

    'Region input
    With Sheets("Input Sheet")
        With .Range("C13").Validation
            .Delete
            .Add Type:=xlValidateList, Formula1:="=" & Rng3.Address(External:=True)
        End With

        'Capability input
        With .Range("U13").Validation
            .Delete
            .Add Type:=xlValidateList, Formula1:="=" & Rng1.Address(External:=True)
        End With

        'Certification input
        With .Range("L13").Validation
            .Delete
            .Add Type:=xlValidateList, Formula1:="=" & Rng2.Address(External:=True)
        End With
    End With

 

[Jonmo1]

I think the problem is that the Address property of a range, only returns the Range Address as a string (not including the sheetname)

So if say Rng1 was set to 'Data Validation'!A1:A100
Then Rng.1 Address is only equal to "$A$1:$A$100" <- No sheet name.

So your autofilter line goes from
.Add Type:=xlValidateList, Formula1:="=" & Rng1.Address & ""
To
.Add Type:=xlValidateList, Formula1:="=$A$1:$A$100"
When you actually want
.Add Type:=xlValidateList, Formula1:="='Data Validation'!$A$1:$A$100"


You'll have to concatenate the SheetName into that line..
.Add Type:=xlValidateList, Formula1:="='" & Rng1.Parent.Name & "'!" & Rng1.Address

 

[Jonmo1]

You can also do

.Add Type:=xlValidateList, Formula1:="=" & Rng1.Address(, , , True)

 

[CPDan]

Perfect that solved it! Thank you very much, have a good day!

 

[Jonmo1]

You're welcome.