Excel VBA - Comparing rows in same column

Question

I am new to excel VBA and I have a task I need to complete using VBA. I am looking to compare values in the same column. I want to start the comparison with the last row and move up. The criteria for filtering is if the % difference between the current and last number is greater than 3% then copy and paste the value to another row. Once a value is copied and pasted, values in the data should be compared to the previous copied and pasted value when checking for the 3% difference. Example below. Thanks in advance.

For example if my data range is shown below

This should be my result:

The results I have right now have 3005 in there (the difference between 3000 and 3005 is less than 3% percent (3005/3000) thus 3005 should not be in the list), when it should not be in the list.

This is the code I have at the moment. Thanks in advance.

Sub main2()

Dim row_a As Long
Dim row_b As Long
Dim l_2

row_b = Range("D5000").End(xlUp).Row
Cells(row_b, "d").Copy Cells(row_b, "P")

l_2 = row_b - 1

For i = row_b To 3 Step -1
    a = Cells(row_b, "d").Value
    For j = l_2 To 3 Step -1
        If a / Cells(j, "d") <= 0.97 Or a / Cells(j, "d") >= 1.03 Then
            Cells(j, "d").Copy Cells(j, "p")
            a = Cells(j, "d").Value
        End If
    Next j
Next i

End Sub

as per your own specification, 3005 should be on the list because the difference between 3005 and 1290 and the difference between 3005 and 1500 are both greater than 3% ... please rethink your specification — jsotola
– jsotola, Commented Sep 22, 2017 at 23:06
@Jonathon-Sidwell, It would be helpful for any reader of this post if you could describe at least what for you are using your query. as there seem to be some difficulties to specify the wanted approach. — T.M.
– T.M., Commented Sep 25, 2017 at 8:27

Dinesh Vilas Pawar · Accepted Answer · 2017-09-23 16:34:54Z

1

@Jonathon As I go through your code and found that you need to select value in column "D" as like,

if value is selected then no any value select near by 3% of any selected value

and selection criteria goes from bottom to up which come first take it as you suggested in (3000 an 3005 problem)

and paste all selected value in column "P"

if it correct then go through following code it satisfied your given condition as per question

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' 'Code Start here

Sub Filter3Per()

Dim LastRow As Integer
Dim ComVal As String


'''''''''Apply filter on columun with loop as per criteria
'Read last Row from D column
LastRow = Cells(Rows.Count, "D").End(xlUp).Row

'Clear format color of column D
Range("D:D").Interior.ColorIndex = -4142

'''Clear P column
Range("P:P").ClearContents
'Loop Goes from botttom to top 3 row
For i = LastRow - 1 To 1 Step -1
    'Read compvalue
    ComVal = Cells(i + 1, "D").Value

    'Check for color
    If Cells(i + 1, "D").Interior.ColorIndex <> 3 Then

        'Loop to Check as Criteria
        For j = i To 1 Step -1

        'Critera
        If ComVal / Cells(j, "D") <= 0.97 Or ComVal / Cells(j, "D") >= 1.03 Then

        Else
        Cells(j, "D").Interior.ColorIndex = 3

        End If
        Next

    End If

Next

''''''''Apply filter on columun with loop as per criteria End here
'''''''''''''''Collect value''''''''''''''''''''
'''Clear P column

Range("P:P").ClearContents
For i = 1 To LastRow

    If Cells(i, "D").Interior.ColorIndex <> 3 Then

     Cells(i, "P").Value = Cells(i, "D") 'add value in p Column

    End If
Next
'''''''''''Collect value end here
End Sub

'sub end here '''''

answered Sep 23, 2017 at 16:34

Dinesh Vilas Pawar

4932 gold badges5 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

T.M. Over a year ago

Always use fully qualified range references, e.g. Dim ws As WorkSheet followed by Set ws = ThisWorkBook.WorkSheets("MySheetName"). Then you can refer precisely to ws.Cells(i+1,"D") for example, instead of Cells alone.

T.M. Over a year ago

It's a nice idea to mark exclusion values by an interior Color index instead of using an extra column.

T.M. Over a year ago

your ComVal declaration as string will result in a type error.

Dinesh Vilas Pawar Over a year ago

@TM yes it is as long

Collectives™ on Stack Overflow

Excel VBA - Comparing rows in same column

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related