lundi 26 août 2019

Multiple criteria if statement to check for one of three values, and replace value if not one of the three?

I am trying to add an if statement in my VBA macro that will check the value in all rows within a table for a particular column.

I'm trying to have the macro perform the check, and if the cell value is not one of three choices, then the value of the cell & the cell to the left of it will be replaced by the values of two adjacent columns.

In other words, if B1 doesn't say "RED" or "BLUE" or "GREEN", then I want the macro to change A1=C1 and B1=D1.

This is for text values (specifically, names) which I think might be giving me issues.

Dim rng As Range

Set rng = Range("CS2:CS100")

For Each cell In rng

    If cell.Value <> "NAME1" And cell.Value <> "NAME2" And cell.Value <> "NAME3" Then
        Range("CW" & ActiveCell.Row & ":CX" & ActiveCell.Row).Copy
        Range("CR" & ActiveCell.Row & ":CS" & ActiveCell.Row).PasteSpecial xlPasteValues
    End If

Next cell

I'm not getting any error messages, however, the cell values are not changing. Am I using the 'And' logic incorrectly?

Aucun commentaire:

Enregistrer un commentaire