Error 1004 with VBA and validation

[stocktsi]

I'm trying to dynamically add validation to my Excel spreadsheet using VBA so
I can use logic to figure out which cells get what validation. I'm using the
following code to try to validate the user entering an R, E, or P:

With Worksheets("Input").Cells(copyRow, copyCol).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:=xlBetween, Formula1:="=R, E, P"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

When I get to the .add line, I get an error 1004 (all the other lines work
fine if I switch the order). I've tried both Formula1:="=R, E, P" and
Formula1:="R, E, P" with the same result.

Any guidance on how to fix this? Thanks...

 

[OssieMac]

You are missing the Delete. It will work the first time without it but any
changes must include it. Best to always include it.

With Selection.Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:="R,E,P"

 

[stocktsi]

Thanks - that worked. Sometimes it's the easiest things - I couldn't tell
you how long I was hitting my head against the wall on this...