Compare Cell Values

JonRowland

Active Member
Joined
May 9, 2003
Messages
395
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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,208
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
 

JonRowland

Active Member
Joined
May 9, 2003
Messages
395
Office Version
  1. 365
Platform
  1. Windows
mikerickson,

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

Thx
Jon
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,928
Messages
5,834,410
Members
430,283
Latest member
MikeHoyt

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
Top