Runtime error 438
 

I am getting a runtime error 438 at the line between asterisks - can anyone
tell me why?

For Each MyCell In Range("C71:K71")

If MyCell.Value = vbNullString Then

With MyCell.Offset(1)
.Validation.Delete
.Value = vbNullString
.Locked = True
End With

With MyCell.Offset(2)
.Validation.Delete
.Value = vbNullString
.Locked = True
End With

ElseIf MyCell.Value < "" Then

With MyCell.Offset(1)
.Locked = False
With .Validation
.Delete
.Add Type:=xlValidateList, Formula1:="Boundary,Lost
Ball,Water,Unplay,Other"
.IgnoreBlank = True
.InCellDropdown = True
End With
End With
End If

If MyCell.Offset(1).Value = vbNullString Then

With MyCell.Offset(2)
.Validation.Delete
.Value = vbNullString
.Locked = True
End With

ElseIf MyCell.Offset(1).Value < "" Then

With MyCell.Offset(2)
.Locked = False
.Validation.Delete
*******
.Add Type:=xlValidateList, Formula1:="Boundary,Lost
Ball,Water,Unplay,Other"
*******
.IgnoreBlank = True
.InCellDropdown = True
End With
End If

Next MyCell

Thanks
Sandy

Runtime error 438
 

Excel may like to know what you are adding.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Sandy" wrote in message
...
I am getting a runtime error 438 at the line between asterisks - can anyone
tell me why?

For Each MyCell In Range("C71:K71")

If MyCell.Value = vbNullString Then

With MyCell.Offset(1)
.Validation.Delete
.Value = vbNullString
.Locked = True
End With

With MyCell.Offset(2)
.Validation.Delete
.Value = vbNullString
.Locked = True
End With

ElseIf MyCell.Value < "" Then

With MyCell.Offset(1)
.Locked = False
With .Validation
.Delete
.Add Type:=xlValidateList, Formula1:="Boundary,Lost
Ball,Water,Unplay,Other"
.IgnoreBlank = True
.InCellDropdown = True
End With
End With
End If

If MyCell.Offset(1).Value = vbNullString Then

With MyCell.Offset(2)
.Validation.Delete
.Value = vbNullString
.Locked = True
End With

ElseIf MyCell.Offset(1).Value < "" Then

With MyCell.Offset(2)
.Locked = False
.Validation.Delete
*******
.Add Type:=xlValidateList, Formula1:="Boundary,Lost
Ball,Water,Unplay,Other"
*******
.IgnoreBlank = True
.InCellDropdown = True
End With
End If

Next MyCell

Thanks
Sandy

Runtime error 438
 

Sandy,

Make it:

Validaion.Add Type:=xlValidateList, Formula1:="Boundary,Lost
Ball,Water,Unplay,Other"

hth,

Doug

"Sandy" wrote in message
...
I am getting a runtime error 438 at the line between asterisks - can anyone
tell me why?

For Each MyCell In Range("C71:K71")

If MyCell.Value = vbNullString Then

With MyCell.Offset(1)
.Validation.Delete
.Value = vbNullString
.Locked = True
End With

With MyCell.Offset(2)
.Validation.Delete
.Value = vbNullString
.Locked = True
End With

ElseIf MyCell.Value < "" Then

With MyCell.Offset(1)
.Locked = False
With .Validation
.Delete
.Add Type:=xlValidateList, Formula1:="Boundary,Lost
Ball,Water,Unplay,Other"
.IgnoreBlank = True
.InCellDropdown = True
End With
End With
End If

If MyCell.Offset(1).Value = vbNullString Then

With MyCell.Offset(2)
.Validation.Delete
.Value = vbNullString
.Locked = True
End With

ElseIf MyCell.Offset(1).Value < "" Then

With MyCell.Offset(2)
.Locked = False
.Validation.Delete
*******
.Add Type:=xlValidateList, Formula1:="Boundary,Lost
Ball,Water,Unplay,Other"
*******
.IgnoreBlank = True
.InCellDropdown = True
End With
End If

Next MyCell

Thanks
Sandy

Runtime error 438
 

Don
Thank you
Wood and Trees spring to mind

Doug
I realised after I saw Don's comment
Thanks Doug

Sandy

"Sandy" wrote in message
...
I am getting a runtime error 438 at the line between asterisks - can
anyone tell me why?

For Each MyCell In Range("C71:K71")

If MyCell.Value = vbNullString Then

With MyCell.Offset(1)
.Validation.Delete
.Value = vbNullString
.Locked = True
End With

With MyCell.Offset(2)
.Validation.Delete
.Value = vbNullString
.Locked = True
End With

ElseIf MyCell.Value < "" Then

With MyCell.Offset(1)
.Locked = False
With .Validation
.Delete
.Add Type:=xlValidateList, Formula1:="Boundary,Lost
Ball,Water,Unplay,Other"
.IgnoreBlank = True
.InCellDropdown = True
End With
End With
End If

If MyCell.Offset(1).Value = vbNullString Then

With MyCell.Offset(2)
.Validation.Delete
.Value = vbNullString
.Locked = True
End With

ElseIf MyCell.Offset(1).Value < "" Then

With MyCell.Offset(2)
.Locked = False
.Validation.Delete
*******
.Add Type:=xlValidateList, Formula1:="Boundary,Lost
Ball,Water,Unplay,Other"
*******
.IgnoreBlank = True
.InCellDropdown = True
End With
End If

Next MyCell

Thanks
Sandy