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