application defined error - VBA

[murugavelmsc]

Hi,

I have an excel sheet that is loaded with a dynamic result set of data. Column C2 depends on A2 Column data. The following code throws an 'Applicaton-defined or object-defined error':

Sheet1.Activate
With ActiveWorkbook.ActiveSheet.Range("C2").Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:= _
"=OFFSET(regionstart, MATCH(A2, BookElement,0)-1,1,COUNTIF(BookElement,A2),1)"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "Error"
.InputMessage = ""
.ErrorMessage = "You can only choose from the list."
.ShowInput = True
.ShowError = True
End With
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C1000"), Type:=xlFillDefault

bye
Murugavel

[MikeTheBike]

Hi

I strongly suspect that the 'MATCH(A2, BookElement,0)' part of the condition formula returns an error, but obviously I can not prove this!?

MTB