VBA code for comparison RRS feed

  • Question

  • In a sheet I have following column name

    Table1                               Compare Field                    Table2

    Field Name  Datatype Reg       Result              Field Name  Datatype Reg

    Akshay          int          Yes             True                xyz          flot       no  

    Compare Button

    xyz              int             No              False              Akshay    int     Yes

    I have to compare table1 all three column data with table2 all three colum data. Table2 data can be present at any row I have to search and compare it.

    If all the column data matches I have to print status True in Compare Field Column after Table1 data

    Code will start executing after clicking on Compare Button

    Friday, September 15, 2017 7:13 AM

All replies

  • Hi Akshay Chavan 07,

    You could try and adjust below code for your need.

    Private Sub CommandButton1_Click()
    Dim tbl1Rng As Range
    Dim tbl2Rng As Range
    Set tbl1Rng = ActiveSheet.ListObjects(1).Range
    Set tbl2Rng = ActiveSheet.ListObjects(2).Range
    For i = 2 To tbl1Rng.Rows.Count
    name1 = tbl1Rng.Cells(i, 1)
    type1 = tbl1Rng.Cells(i, 2)
    reg1 = tbl1Rng.Cells(i, 3)
    For j = 2 To tbl2Rng.Rows.Count
    name2 = tbl2Rng.Cells(j, 1)
    type2 = tbl2Rng.Cells(j, 2)
    reg2 = tbl2Rng.Cells(j, 3)
    If name1 = name2 And type1 = type2 And reg1 = reg2 Then
            tbl1Rng.Cells(i, 1).Offset(0, 3) = "True"
            GoTo NextRow
            End If
        Next j
    tbl1Rng.Cells(i, 1).Offset(0, 3) = "False"
    Next i
    End Sub

    Best Regards,


    Friday, September 15, 2017 9:52 AM
  • Excel 2010/2013/2016 Power Query (aka Get & Transform)
    No formulas, no VBA macro.
    Table2 can be sorted and arranged randomly
    and contain fewer or more names than Table1.
    Click on "Refresh All"

    Friday, September 15, 2017 4:43 PM
  • Thanks for reply

    Sub Button2_Click()
    Dim tbl1Rng As Range

    Dim tbl2Rng As Range
    Dim tbl3Rng As Range
    Dim name1, type1, reg1, name2, type2, reg2 As Variant

    Set tbl1Rng = ThisWorkbook.ActiveSheet.Range(Cells(1, 3), Cells(1, 3000))
    Set tbl2Rng = ThisWorkbook.ActiveSheet.Range(Cells(5, 3), Cells(5, 3000))

        For i = 3 To tbl1Rng.Count
            name1 = Cells(i, 1)
            type1 = Cells(i, 2)
            reg1 = Cells(i, 3)
            For j = 3 To tbl2Rng.Count
                name2 = Cells(j, 5)
                type2 = Cells(j, 6)
                reg2 = Cells(j, 7)
                If name1 = name2 And type1 = type2 And reg1 = reg2 Then
                    Cells(i, 4) = "True"
                GoTo NextRow
                End If
            Next j
            Cells(i, 4) = "False"
            'tbl1Rng.Cells(i, 1).Offset(0, 3) = "False"
        Next i

    End Sub

     little bit modification to your code

    Saturday, September 16, 2017 8:48 AM
  • Hi Akshay Chavan 07,

    Thanks for sharing your solution. Has your original issue been solved?

    If it has, I would suggest you mark useful reply or your solution to close this thread.

    If not, please feel free to let know us know your current issue.

    Best Regards,


    Monday, September 18, 2017 1:23 AM