Compare Cell Values

JonRowland

Active Member
Joined
May 9, 2003
Messages
415
Office Version
  1. 365
Platform
  1. Windows
Hi,

I'm running a loop to run through a series of rows in order to identify duplicate rows and then delete then.

So what the code will look for is a time within 2 seconds contained within row B.

If this is true then to check values in C & D are same. However values in C&D will only be unique for the last 10 digits so I thought the RIGHT function would be good to use but when running this bit of code I get a run-time error 424 "OBJECT REQUIRED"

<code>

Row = 2
LastRow = Cells.Find("*", Cells(1), xlFormulas, xlWhole, xlByRows, xlPrevious).Row
Do
Deleted = False
TIME1 = Cells(Row, 2)
TIME2 = Cells(Row + 1, 2)
DIFFERENCE12C = DateDiff("s", TIME1, TIME2)

Cells(Row, 1).Select

If DIFFERENCE12C <= 2 And DIFFERENCE12C >= -2 Then

cct1 = Right(Cells(Row, 3 + columnoffset), 10).Value
cct2 = Right(Cells(Row, 3 + columnoffset), 10).Value
rct1 = Right(Cells(Row, 4 + columnoffset), 10).Value
rct2 = Right(Cells(Row + 1, 4 + columnoffset), 10).Value
ez1 = Cells(Row + 1, 11)

If cct1 = cct2 And rct1 = rct2 Then
If ez1 > 0.5 Then
Cells(Row, 6).Value = "ez1"
End If
Rows(Row + 1).Delete
Deleted = True
Else
End If
End If

If Deleted = False Then Row = Row + 1
If Deleted = True Then LastRow = LastRow - 1

Loop Until Row > LastRow
</code>

I've read that I may need to use a SET statement but not sure what I need to do so any help would be welcome.
Thx
Jon
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
The Right function returns a string. Strings do not have .Value properties.

Perhaps
Code:
Row = 2
LastRow = Cells.Find("*", Cells(1), xlFormulas, xlWhole, xlByRows, xlPrevious).Row

For Row = LastRow To 2 Step -1
    
    TIME1 = Cells(Row, 2)
    TIME2 = Cells(Row + 1, 2)
    
    DIFFERENCE12C = DateDiff("s", TIME1, TIME2)
    
    If Abs(DIFFERENCE12C) <= 2 Then
    
        cct1 = Right(CStr(Cells(Row, 3 + columnOffset).Value), 10)
        cct2 = Right(CStr(Cells(Row + 1, 3 + columnOffset).Value), 10)
        rct1 = Right(CStr(Cells(Row, 4 + columnOffset).Value), 10)
        rct2 = Right(CStr(Cells(Row + 1, 4 + columnOffset).Value), 10)
        ez1 = CStr(Cells(Row + 1, 11).Value)
    
        If cct1 = cct2 And rct1 = rct2 Then
            If ez1 > 0.5 Then
                Cells(Row, 6).Value = "ez1"
            End If
            Rows(Row + 1).Delete
        End If
    End If
Next Row
 
Upvote 0
mikerickson,

Something new to add to my developement VBA knowledge. Works perfectly.

Thx
Jon
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top