Validation.Formula1 error

[croco1]

Hi all,

I get an run-time error 1004 in the code below in row: If rCell.Validation.Formula1 = "EUR" Then

Can somebody help me?


Sub test()
Dim rCell As Range
If Selection.Cells.Count = 1 Then
MsgBox "Select the range to be processed."
Exit Sub
End If
For Each rCell In Selection
If rCell.Validation.Formula1 = "EUR" Then
rCell.Validation.Delete
rCell.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=ListCurrency"
End If
Next rCell
End Sub

 

[Momentman]

When a cell does not contain any validation errors, it throws up an error. Thats what is happening

If thats the only possible cause of an error(a big assumption), then

Sub test()
    Dim rCell As Range
    If Selection.Cells.Count = 1 Then
        MsgBox "Select the range to be processed."
        Exit Sub
    End If
    For Each rCell In Selection
        On Error Resume Next
        If rCell.Validation.Formula1 = "EUR" Then
            rCell.Validation.Delete
            rCell.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=ListCurrency"
        End If
    Next rCell
End Sub

 

[croco1]

Unfortunately the validation with formula1:="=ListCurrency" is applied to EACH cell in the selected area, while it only needs to change the cells which already have a validation with formula1:="EUR".

 

[steve the fish]

If all cells need validation listcurrency why not delete all validation and reapply?

 

[croco1]

I have a large workbook and only CERTAIN cells have a validation. I need to replace the validation in the cells that have "EUR" in formula1 and only replace these with "=ListCurrency", while ommiting cells without validation or with different validation than "EUR". The workbook is too large to reapply manually the specific cells.

 

[steve the fish]

Make your mind up. You just said:

'Unfortunately the validation with formula1:="=ListCurrency" is applied to EACH cell in the selected area'

Which is it?

 

[croco1]

Sorry for being unclear. 'Unfortunately the validation with formula1:="=ListCurrency" is applied to EACH cell in the selected area' was my response to Momentman's solution. So it should only apply to certain cells ("EUR") while his solution enters a validation.formula1 with "=ListCurrency" in all cells in a selected area.

 

[steve the fish]

See if this does it:

On Error GoTo 0
Set Rng = Selection.SpecialCells(xlCellTypeAllValidation)
With Rng.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=ListCurrency"
End With

 

[croco1]

It now changes all cells that have a validation. I would like to only change the ones with Formula1:="EUR".

 

[steve the fish]

Ok lets try:

On Error GoTo 0
Set Rng = Selection.SpecialCells(xlCellTypeAllValidation)
If Not Rng Is Nothing Then
    For Each c In Rng
        With c.Validation
            If .Formula1 = "EUR" Then
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                    xlBetween, Formula1:="=ListCurrency"
            End If
        End With
    Next
End If