Validaion =INDIRECT VBA Problem

[Brettlb]

Using excel 2000, I am trying to insert a data validation list using the following code:

Range("C8").Select
Selection.Name = "SuburbV"
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=INDIRECT($A$6)"
.IgnoreBlank = True
.InCellDropdown = True
End With

However, the argument Formula1:="=INDIRECT($A$6)" produces the following error:

Run Time Error 1004:
Application-defined or object-defined error


The code works when I change the formula to :="A1:A6" , however this does not produce the same result that I require with the indirect statement.

Any suggestions?

 

[TommyGun]

This errors because the INDIRECT function will return an error if a valid range address does not exist in A6. Make sure that there is a valid address there before running this code.

 

[Brettlb]

Thanks for you reply.

A6 contains the formula:

="_"&Lookups!B1

and produces the result of _2481, where Lookups is an additional sheet.

_2481 is a range name that refers to =Sheet1!$P$3:$P$4, where sheet1 is the current sheet where I wish to insert the validation.

I have tried typing _2481 into cell A6. (same error)
I have inserted the formula =_2481 into A6, but this returns only the first record in the range (unsuitable).

 

[Todd Bardoni]

Maybe I'm talking out of ignorance here, but shouldn't your formula look like this: INDIRECT("A6") or something like this: =INDIRECT(RC[-1]) or even: =INDIRECT(R6C1)