Get Rid of the False Positive Results of the Code.

To Whom It May Concern,

I try to replace 5 cells of Row N to "Same As Above" when the values of these cells of Row N are same as those of Row N-1 respectively.

However, I always have problem with false positive results, i.e. they are not the same, but replaced anyway.

PHP Code: 

Sub Test_Lease()
'
' Test_Lease Macro
'
'   Proper all worksheet
        Dim ws As Object
        Dim LCell As Range
        Dim X As Long
        Dim Lr As Long
        X = ActiveSheet.UsedRange.Rows.Count
        Lr = Cells(Rows.Count, "A").End(xlUp).Row + 1
        
       
        Application.ScreenUpdating = 0
'   Delete unwanted columns
        Columns("B:B").Select
        Selection.Delete
        
'   Move Last Prod next to First Prod
        Columns("U:U").Select
        Selection.Cut
        Columns("S:S").Select
        Selection.Insert Shift:=xlToRight
        
'   Format Columns
        Columns("T:X").Select
        Selection.Style = "Comma"
        Selection.NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
        Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
        
'   Ranking *** BOE Descending
    Range("X2").Select
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range( _
        "X2"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.ActiveSheet.Sort
        .SetRange Range("A2:Z" & X)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range( _
        "X2:X" & X), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range( _
        "D2:D" & X), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.ActiveSheet.Sort
        .SetRange Range("A1:Z" & X)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
        
'   Replace Duplicate Lease Production
    X = ActiveSheet.UsedRange.Rows.Count
    Y = 0
    Range("T" & X, "X" & X).Select
    For n = 1 To X - 1
        For Each cell In Selection
            If cell = cell.Offset(-1, 0) Then
                Y = Y + 1
            End If
            If Y = 5 Then
                Selection.Value = "Same As Above"
            Y = 0
            End If
        Next
    Selection.Offset(-1, 0).Select
    Next

        Application.ScreenUpdating = True
        
        
End Sub 


Please refer to the code & attachment.

The false positive will be on Row 126 & Column T-X after you run the code. Please help.

Thanks in advance for your help.

Best regards,

Matt

Very messy code but the problem lies at:

Please Login or Register  to view this content.

Hi Millz,

Thank you very much for the help. Problem solved. You made my day. Thanks again.

All the best,

Matt