CODEWITHSUNDEEP
vbaexcel
Himani Sancheti
Himani Sancheti

Reputation: 11

VBA: How to compare values of changing cells in a column with another dynamic range?

I have a list of 500 orders, each with a product code. I don't know how many different product codes exist (there are multiple orders for the same product code). I need to write a Macro code to find all the existing product codes and paste them in the column L.

The first product code will be pasted in column L as is. Now, as this continues I want the next product code from column C to be checked with all the values that have been entered in column L, i.e., for eg, if A01 has already been pasted in column L then when A01 occurs the next time in Column C, I don't want it to be pasted in Column L.

What I mean is I want to find all the unique product codes I have. I have accomplished this by recording a data filter (Ascending) marco but I want to know how this can be done by writing my own code. Thanks a lot in advance! :)

Sub try()

    Dim j as long

    Dim i As Long, lastrow As Long



    lastrow = Workbooks("A.xlsx").Worksheets("Sheet1").Range("A1048576").End(xlUp).Row
    On Error GoTo 0

    Range("C2").Select
    Selection.Copy Workbooks("A.xlsx").Worksheets("Sheet1").Range("L2")

    For i = 3 to lastrow

    j = Workbooks("A.xlsx").Worksheets("Sheet1").Range("L1048576").End(xlUp).Row + 1

       If Range("C" & i).value <> Range("L2:L" & j - 1).value Then 'Here's what I don't know how to write

       Range("C" & i).select

       Selection.Copy Workbooks("A.xlsx").Worksheets("Sheet1").Range("L" & j)

    Next i

End Sub

Upvotes: 1

Views: 112

Answers (1)

A.S.H
A.S.H

Reputation: 29332

Copy all codes into the target column then remove the duplicates from it:

Sheet1.Columns("C").Copy Sheet1.Columns("L")
Sheet1.Columns("L").RemoveDuplicates 1

Upvotes: 2

Related Questions