hi bernie

the formula im trying to apply gives a dynamic range so that you can add to
the range without re-writing the reference

http://www.cpearson.com/excel/named.htm

the resize option is a new one to me that ill remember but isnt as flexible.
i have used "advanced filter unique records" to create the lists im refering
to so i dont know in advance how many rows each will have. currenly the lists
a accross the spreadsheet whilst the cells im validating are down a column
making copying the formula imposible. im going to re-arrange the lists so
they are in the same plane

hope this helps you understand what im am trying to do

"Bernie Deitrick" wrote:

Pete,

Yes, I have an idea: you need to pass a valid range reference to the formula1 property of the
validation object.
For example:

Dim rowcount As Integer
rowcount = 3

With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & Cells(241, 1).Resize(rowcount, 1).Address
End With

But I have no idea what you are trying to do with the Match * formula. Explain the logic of your
list selection, and then we'll go from there. Until then, I'm stuck .

HTH,
Bernie
MS Excel MVP


"pete the greek" wrote in message
...
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