VBA Validation error

[Donai]

Hi, i am getting application object defined error on the below, could you please explain why?

With .Range("A6:A" & lDstRowNum).Validation
                    .Delete
 [COLOR=red][B]                   .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=rngName"
                End With[/B][/COLOR]

 

[Donai]

*bump*

 

[shg]

Works fine for me. You might delete the Operator argument, which isn't needed:

    With Range("A6:A" & lDstRowNum).Validation
        .Delete
        .Add Type:=xlValidateList, _
             AlertStyle:=xlValidAlertStop, _
             Formula1:="=rngName"
    End With

 

[Donai]

Is it because the named range is dynamic?

Works fine for me. You might delete the Operator argument, which isn't needed:

    With Range("A6:A" & lDstRowNum).Validation
        .Delete
        .Add Type:=xlValidateList, _
             AlertStyle:=xlValidAlertStop, _
             Formula1:="=rngName"
    End With

 

[Donai]

When i run in excel 2010 the code runs perfectly but when i run in 2002 i get an error. Also i am running the code through command button macro but if i run the macro on it own through ALT F8 then it works, very strange.

 

[Donai]

This is the dynamic formula "rngName" am i correct to use this formula for my list?

=OFFSET(Assumptions!$E$20,0,0,COUNTA(Assumptions!$E:$E),1)

 

[Donai]

Could it be something to do with

TakeFocus on click? Should this be set to FALSE?

 

[shg]

When i run in excel 2010 the code runs perfectly but when i run in 2002

I don't have either of those versions.

=OFFSET(Assumptions!$E$20,0,0,COUNTA(Assumptions!$E:$E),1)

That's a volatile range name. You might replace it with

=$E$20:INDEX($E:$E, MATCH(9E307, $E:$E))

or

=$E$20:INDEX($E:$E, MATCH(REPT("z", 255), $E:$E))

... depending on whether there are numbers or text in the range.

 

[Donai]

Ok i tried that but i still get 1004-application object defined error. Everytime i open the file and run the macro i get the error, but the second time i run it works. This is only in Excel 2002 not 2010, and only when i run the code from another sheet via command button code.

Is there another method to add list?

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=rngName"

I don't have either of those versions.



That's a volatile range name. You might replace it with

=$E$20:INDEX($E:$E, MATCH(9E307, $E:$E))

or

=$E$20:INDEX($E:$E, MATCH(REPT("z", 255), $E:$E))

... depending on whether there are numbers or text in the range.