Specify Range of cells for .validation

Question

I would like to insert a data validation list that grabs data from another sheet. the column the list will be generated from is stored in another variable that is dynamic. My code so far is:

pRange = Sheets("Payer Output").Cells(24, 3).Value

With Sheets("Payer Output").Range("C23").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=Sheets("Payers in Top 4").Cells(3, pRange)
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

This formula generates a list, and looks in the correct column, however the current code only specifies the list should look in a single cell, so when the list is generated, there is only one value. How do I specify a range of cells for the 'formula1:=' line? I've tried:

Formula1:=Sheets("Payers in Top 4").Range(Cells(3, pRange), Cells(10,pRange))

But this does not work. Thank you for your help!

Answer 1

Sometimes I will just name the range and put the named range in the list.

Sub AddDtaVal()
      pRange = Sheets("Payer Output").Range("C24").Value

    Sheets("Payers in Top 4").Range(Sheets("Payers in Top 4").Cells(3, pRange), Sheets("Payers in Top 4").Cells(10, pRange)).Name = "List"

    With Sheets("Payer Output").Range("C23").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
             xlBetween, Formula1:="=List"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

Answer 2

You need to build the address formula in a string, Excel-style : 'Sheet Name'!A1:A10

Dim StrFormula As String
Dim pRange As Long

pRange = Sheets("Payer Output").Cells(24, 3).Value
With Sheets("Payers in Top 4")
    StrFormula = "'" & .Name & "'!" & .Range(.Cells(3, pRange), .Cells(10, pRange)).Address
End With

With Sheets("Payer Output").Range("C23").Validation
    .Delete
    .Add Type:=xlValidateList, _
            AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, _
            Formula1:=StrFormula
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With

Answer 3

Try this

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Operator:=xlBetween, Formula1:="='Payers in Top 4'!" & Worksheets("Payers in Top 4").Cells(3, pRange).Resize(8).Address