Excel comparing multiple cells with vba

Question

I need help here with Excel VBA.

I want to know how to compare multiple cells in the same row, but in different columns.

I need to compare something like specific cells E3, G3, I3 and find out if any of it are duplicates and remove the duplicate of whichever was put in first.

Note: A drag and drop function is used to fill in the data.

I tried using something like this

Dim R1, R2, R3 As Range 
Set R1 = Range("E3")
Set R2 = Range("G3")
Set R3 = Range("I3")

If (R1 = R2 Or R1 = R3) Then 
MsgBox "Room Already Used!" 
            With Application
                .EnableEvents = False
                .Undo
                .EnableEvents = True
            End With
End If

But I need to compare even more rows and columns.

Siddharth Rout · Accepted Answer · 2016-08-03 05:14:09Z

First things first.

Dim R1, R2, R3 As Range

You can do this in VB.Net and all three will be declared as a Range but in VBA, you have to explicitly declare them as Range else the first two will be declared as a Variant

Dim R1 As Range, R2 As Range, R3 As Range

To compare multiple rows and columns you can use a loop. For example

With Sheet1 '<~~ Change this to the relevant sheet
    For i = 3 To 8 '<~~ You actual row numbers
        If .Range("E" & i).Value = .Range("G" & i).Value Or _
           .Range("E" & i).Value = .Range("I" & i).Value Then
            '~~> do something
        End If
    Next i
End With

Also instead of Drag & Drop, I would recommend using a Data Validation List so that you do not have to use .Undo. You can simply clear the contents of the cell.

I tried this out and it worked out if it is not drag and drop, but the client wants the worksheet to be a drag and drop type.

JBStovers · Accepted Answer · 2016-08-03 06:41:52Z

0

No need for VBA here. Set a custom data validation rule for cell E3 with the following formula:

=OR(ISBLANK(E3),COUNTIF($E3:$I3,E3)=1)

Then copy the cell to G3 and I3, and copy row 3 down as far as you need. You could then set a custom error message using the Data Validation wizard.

The above formula assumes that you only need to check values across the same row (as you state).

answered Aug 3, 2016 at 6:41

JBStovers

3183 silver badges13 bronze badges

Collectives™ on Stack Overflow

Excel comparing multiple cells with vba

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related