Irregular VBA error with data validation
 

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

Irregular VBA error with data validation
 

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" wrote:

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

Irregular VBA error with data validation
 

Thanks for the reply

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

"keepitcool" wrote in message
...
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" wrote:

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