I have a cell that uses data validation (list) which points at a named range
that represents a range on a different worksheet.

I need to be able to reset the cell value to the first value on the named
range list. Here is my sample code, which isn't working:

S = Sheet12.Range("E2").Validation.Formula1
Sheet12.Range("E2").Value = Range(S)(1)

debug.print Sheet12.Range("E2").Validation.Formula1
results in the named range
=DatesForSelection14days

when I use the immediate window to set:
S = Sheet12.Range("E2").Validation.Formula1
then
debug.print Range(S)(1) I get
10/3/2008
which is correct.

But when the two lines of code run within the sub itself, on the second line
I get a run-time error 1004, method 'Range' of object '_worksheet' failed
huh?

Any advice greatly appreciated.
Thanks,
Keith

Probably due to S being "=DatesForSelection14days". I think you need to drop
the "=" sign


s = Sheet12.Range("E2").Validation.Formula1
s = Mid$(s, 2, Len(s))
Sheet12.Range("E2").Value = Range(s)(1)


"ker_01" wrote:

I have a cell that uses data validation (list) which points at a named range
that represents a range on a different worksheet.

I need to be able to reset the cell value to the first value on the named
range list. Here is my sample code, which isn't working:

S = Sheet12.Range("E2").Validation.Formula1
Sheet12.Range("E2").Value = Range(S)(1)

debug.print Sheet12.Range("E2").Validation.Formula1
results in the named range
=DatesForSelection14days

when I use the immediate window to set:
S = Sheet12.Range("E2").Validation.Formula1
then
debug.print Range(S)(1) I get
10/3/2008
which is correct.

But when the two lines of code run within the sub itself, on the second line
I get a run-time error 1004, method 'Range' of object '_worksheet' failed
huh?

Any advice greatly appreciated.
Thanks,
Keith

hi, Keith !

I could not reproduce the error you are getting -?-

this is working just fine (for me):

With Sheet12.Range("e2")
.Value = Range(.Validation.Formula1)(1)
End With

maybe some crossed/missed/... references to libraries in your vba projet ?

hth,
hector.

__ OP __
I have a cell that uses data validation (list) which points at a named range that represents a range on a different worksheet.

I need to be able to reset the cell value to the first value on the named range list.
Here is my sample code, which isn't working:

S = Sheet12.Range("E2").Validation.Formula1
Sheet12.Range("E2").Value = Range(S)(1)

debug.print Sheet12.Range("E2").Validation.Formula1
results in the named range
=DatesForSelection14days

when I use the immediate window to set:
S = Sheet12.Range("E2").Validation.Formula1
then
debug.print Range(S)(1) I get
10/3/2008
which is correct.

But when the two lines of code run within the sub itself, on the second line
I get a run-time error 1004, method 'Range' of object '_worksheet' failed huh?

Any advice greatly appreciated.
Thanks,
Keith