CODEWITHSUNDEEP
vbaexcel
yassine zagliz
yassine zagliz

Reputation: 33

How to comare two columns using VBA EXCEL

I need a macro VBA that will takes a code in columns "B" and look for it in column "E" row by row until the end and if it exist => Ok in column "F" else "FALSE" There are so many code in columns B and E.

I tried this code but it doesn't works

Sub ComparerCP()
Dim ws1 As Worksheet: Set ws1 = Worksheets("Feuil1")
Dim LastRow As Long
LastRow = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
For i = 1 To LastRow
    If ws1.Cells(i, 2) = ws1.Cells(i, 5) Then 
        ws1.Cells(i, 6) = "OK"
        Else: ws1.Cells(i, 6) = "FALSE"
      End If
Next i
End Sub

Upvotes: 1

Views: 59

Answers (3)

Tom
Tom

Reputation: 9878

You can try this two ways. First VBA:

Public Sub ComparerCP()
    Dim rng As Range
    Dim arr As Variant
    Dim c

    ' Update to your sheet name
    With ActiveSheet
        ' Set the range that we want to check
        Set rng = .Range(.Cells(1, 2), .Cells(.Cells(.Rows.Count, 2).End(xlUp).Row, 2))
        ' This array contains the range to check against
        ' We transpose the array to get a 1D array from the range
        arr = Application.Transpose(.Range(.Cells(1, 5), .Cells(.Cells(.Rows.Count, 5).End(xlUp).Row, 5)))

        ' Loop through check range and test if each value is in the range to check against
        For Each c In rng
            If IsInArray(c.Value2, arr) Then
                c.Offset(0, 1).Value2 = "Ok"
            End If
        Next c
    End With
End Sub


Public Function IsInArray(v As Variant, FilterArray As Variant) As Boolean
    IsInArray = (UBound(Filter(FilterArray, v)) > -1)
End Function

Second, you could just use Excel Formulas. In Column C I've entered:

=IF(COUNT(MATCH(B1,$E:$E,0)),"Ok","")

Which outputs the same thing

Upvotes: 1

Vityata
Vityata

Reputation: 43575

Just guessing that you want to compare each cell with each cell, thus you need a nested loop like this one:

Sub TestMe()

    Dim ws1         As Worksheet
    Dim LastRow     As Long
    Dim LastRowE    As Long
    Dim i           As Long
    Dim ii          As Long

    Set ws1 = Worksheets(1)
    With ws1
        LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
        LastRowE = .Cells(.Rows.Count, "E").End(xlUp).Row
    End With

    For i = 1 To LastRow
        Dim codeFound As Boolean
        codeFound = False
        For ii = 1 To LastRowE
            If ws1.Cells(i, 2) = ws1.Cells(ii, 5) Then codeFound = True
        Next ii

        If codeFound Then
            ws1.Cells(i, 6) = "OK"
        Else
            ws1.Cells(i, 6) = "FALSE"
        End If
    Next i

End Sub

Upvotes: 1

user4039065
user4039065

Reputation:

No need for a loop or even VBA for that matter.

Sub ComparerCP()
    Dim LastRow As Long
    with Worksheets("Feuil1")
        LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row  'thanks Vityata!
        with .range("F1:F" & lastrow)
            .formula = "=if(isnumber(match(B1, E:E, 0)), ""Ok"")"
            'optionally convert formulas to values
            '.value = .value
        end with
    end with
End Sub

Upvotes: 1

Related Questions