How can I loop through columns with specific cell ranges and set data validation in VBA?

Question

I'm new to VBA and I'm trying to create a program to loop through columns with certain cell ranges and create a data validation for those cells. I chose the option "text length"-- I want the minimum to be 1 and the maximum to be a cell reference (however, the cell reference should also change with the respective column). Here is what I have so far -- from using macro recording and editing the code a little.

I set a ranges variable (which should look like R16C2:R500C2 for the first loop), so that it selects the given range in a looped column. "Formula2" should change with the given column, but should remain in row 14.

Any help is appreciated!

Sub Validation()
'
' Validation Macro
For i = 2 To 84
    Dim ranges As String
    ranges = "R16C" + Str(i) + ":R500C" + Str(i)
    With ThisWorkbook.Worksheets(1)

    Columns(i).Select
        Application.Goto Reference:=Range(ranges).Select
        With Selection.Validation
            .Delete
            .Add Type:=xlValidateTextLength, AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, Formula1:="1", Formula2:="=$B$14"
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        End With
    End With

Next i
End Sub

user3598756 user3598756 · Accepted Answer · 2016-11-05T10:23:48

just use

Formula2:="=B$14"

and you don't need any loop either:

Sub Validation()            
    With ThisWorkbook.Worksheets(1).Range("B16:CF500").Validation
        .Delete        
        .Add Type:=xlValidateTextLength, AlertStyle:=xlValidAlertStop, _
        Operator:=xlBetween, Formula1:="1", Formula2:="=B$14"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

How can I loop through columns with specific cell ranges and set data validation in VBA?

2 Answers