Irregular VBA error with data validation

[Dave]

I'm running the following code to add a data validation list (dynamic range)
to a cell. About 30% of the time it spits out an Error 1004 but the rest of
the time it works. I dont do anything differently between the runs

Any ideas?


With Range("D7").Validation
.Delete
.Add Type:=xlValidateList, Formula1:="=val1"
.IgnoreBlank = True
.InCellDropdown = True
End With

 

[keepitcool]

i'm quite sure it must have to do with the "existence"
or validity of the named range (or formula) val.

if that's a relative referenced formula it may throw
an error if called from d7

Sub ValidValidation()
With Range("d7")
.Activate
If IsError(Evaluate("val")) Then
MsgBox "Ouch.. invalid Ref"
Else
With .Validation
.Delete
.Add Type:=xlValidateList, Formula1:="=val"
.IgnoreBlank = True
.InCellDropdown = True
End With
End If
End With
End Sub




keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Dave]

Thanks for the reply

The cell in question was merged - I think the error is gone now that it is
demerged.