Refer to Named Range from Cell Value in For Loop

Question

Good afternoon all,

I'm a bit rusty on my VBA since I have moved onto Programming in other languages, so I was hoping someone would be able to help me.

What I am Tying to Do

I have two sheets. One is the form page which users fill out, the other (Sheet1 .... I didn't name it) is basically a data page.

Within Sheet1 I have a table which shows 1 or 0 depending on if a range on the form has a specific selection. Two columns over the cell value states the range that needs an input message. The Macro is supposed to find all of the 1's, lookup the named range found two columns over and insert the named range. Unfortunately I keep getting an Application or Object Defined Error. My code is below:

PcentData = Sheets("Sheet1").Range("PcentData").Value
    If PcentData > 0 Then
        For Each pCell In Sheets("Sheet1").Range("PcentSwitch")
            If pCell.Value = 1 Then
                With Sheets("Payment-Deduction Form").Range(Cells(pCell.Row, pCell.Column + 2)).Validation 'Error here
                .Add Type:=xlValidateInputOnly
                .InputTitle = "Test"
                .InputMessage = "Test"

                End With
            End If

        Next pCell
    End If

EDIT:

I have managed to ensure the code pulls the named range from the correct sheet by defining a string called NamedRange and having it equal the old with statement, pointing to the correct sheet.

Dim NamedRange As String
PcentData = Sheets("Sheet1").Range("PcentData").Value
    If PcentData > 0 Then
        For Each pCell In Sheets("Sheet1").Range("PcentSwitch")
            If pCell.Value = 1 Then
                NamedRange = Sheets("Sheet1").Cells(pCell.Row, pCell.Column + 2).Value
                MsgBox (Sheets("Sheet1").Cells(pCell.Row, pCell.Column + 2).Value)
                With Sheets("Payment-Deduction Form").Range(NamedRange)
                    If .Validation Then .Validation.Delete
                    .Validation.Add /* Error Here */ Type:=xlValidateInputOnly
                    .InputTitle = "Test"
                    .InputMessage = "Test"
                End With
            End If

        Next pCell
    End If

Unfortunately I get the error Object doesnt support this property or method on the If .validation section.

Answer 1

With help from Vityata I managed to find a solution to my problem. The issue was the original loop was taking the position of the table i wanted to lookup from the wrong sheet. I stored this in a variable called "NamedRange" and plugged that into the With statement instead of trying to code it directly into the Range().

The next problem was the data validation. It seems it does not like to have the .Validation separate from the With statement, as such if I need to do multiple changed to the cell I will need multiple With statements. Here is the working code:

Dim NamedRange As String
PcentData = Sheets("Sheet1").Range("PcentData").Value

    If PcentData > 0 Then
        For Each pCell In Sheets("Sheet1").Range("PcentSwitch")
            If pCell.Value = 1 Then
                NamedRange = Sheets("Sheet1").Cells(pCell.Row, pCell.Column + 2).Value
                With ThisWorkbook.Sheets("Payment-Deduction Form").Range(NamedRange).Validation
                    .Delete
                    .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
                    :=xlBetween
                    .IgnoreBlank = True
                    .InCellDropdown = True
                    .InputTitle = "test"
                    .ErrorTitle = ""
                    .InputMessage = "test"
                    .ErrorMessage = ""
                    .ShowInput = True
                    .ShowError = True
                End With
            End If

        Next pCell
    End If

Thanks for your help!