CODEWITHSUNDEEP
vbaexcel
user6670582
user6670582

Reputation: 21

Excel comparing multiple cells with vba

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.

Image of Spreadsheet

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.

Upvotes: 2

Views: 4744

Answers (2)

JBStovers
JBStovers

Reputation: 318

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).

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149297

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.

Upvotes: 3

Related Questions