using a macro to apply data validation to several cells
hi folks
i would like to use a macro to add data validation to several cells where
the source data for each is in a different list (200+)
i have the code below from recording
Sub Macro6()
'
'
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=offset(a241,0,0,match(""*"",A:A,-1),1)"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub
what i need to be able to change is the cell and column references in the
formula =offset(a241,0,0,match(""*"",A:A,-1),1)"
i have tried building this into a a string variable and substituting this in
the code
string_variable = "=offset(" & col_letter & "241,0,0,match(""*""," &
col_letter & ":" & col_letter & ",-1_1)"
Formula1:=string_variable
to no avail..
i have also tried building the string in situ
any ideas
|