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

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

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

Pete,

Ah, OK.

Dim myCell As Range

For Each myCell In Selection
With myCell.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=OFFSET(" & Cells(241, myCell.Column).Address & ",0,0,COUNTA(" & _
Cells(241, myCell.Column).Resize(1000).Address & "),1)"
End With

HTH,
Bernie
MS Excel MVP


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

Pete,

I hit send before I finished this - someone came into my office, and I sent it out without
remembering to add:

The code is written for having the data validation list at the bottom of the appliacable column,
starting in row 241, extending for up to 1000 values.

Select all the cells that you want to apply CF to, and run the macro.

HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Pete,

Ah, OK.

Dim myCell As Range

For Each myCell In Selection
With myCell.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=OFFSET(" & Cells(241, myCell.Column).Address & ",0,0,COUNTA(" & _
Cells(241, myCell.Column).Resize(1000).Address & "),1)"
End With

HTH,
Bernie
MS Excel MVP


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

hi bernie

i think i have solved my problem by placing my lists in rows rather than
column. this allowed me to copy the data validation from one cell to another
and the cell refs changed correctly.

thanks for taking the time to help ill study your example to further my
knowledge

regards

pete

"Bernie Deitrick" wrote:

Pete,

I hit send before I finished this - someone came into my office, and I sent it out without
remembering to add:

The code is written for having the data validation list at the bottom of the appliacable column,
starting in row 241, extending for up to 1000 values.

Select all the cells that you want to apply CF to, and run the macro.

HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Pete,

Ah, OK.

Dim myCell As Range

For Each myCell In Selection
With myCell.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=OFFSET(" & Cells(241, myCell.Column).Address & ",0,0,COUNTA(" & _
Cells(241, myCell.Column).Resize(1000).Address & "),1)"
End With

HTH,
Bernie
MS Excel MVP


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