Compare multiple columns on 2 seperate sheets and says "Match" or "No Match?

Question

I'm very much a beginner to VBA and i'm trying to create a macro that checks that the values in a specific column on Sheet4 matches on Sheet3. If there is a match I'd like it to put "Match" or "No Match" in Sheet2 starting at D4 and also display a count of the errors in each column in D2:AC2. The current code I have written up just continually runs until excel crashes and all the values come up as "No Match" and I'm not sure what i've done wrong.

Sub ProductCheck()

Dim i, r As Integer
Dim LRow, LRow1 As Long
Dim found As Range

'Copies over all the product names currently set up on demo
Worksheets("Product Rec").Range("B4:AC5000").Clear
Sheets("Demo Product Report").Select
Range("A2:B5000").Copy
Sheets("Product Rec").Select
Range("B4").Select
ActiveSheet.Paste

'Checking if both products are in live and demo
LRow = Sheets(4).Range("A" & Rows.Count).End(xlUp).Row
LRow1 = Sheets(2).Range("B" & Rows.Count).End(xlUp).Row

For i = 2 To LRow
    For r = 4 To LRow1
        If Sheets(4).Range("A" & i).Value = Sheets(3).Range("A" & i).Value Then
            Sheets(2).Range("D" & r).Value = "MATCH"
        Else
            Sheets(2).Range("D" & r).Value = "NO MATCH"
        End If
    Next r
Next i

For more info the first part just copies the names of all the products set up to the table on sheet2 and all the checks are placed in the corresponding columns for the products. This is the output table

possible to provide sample excel without any sensitive info? — Bharat, Jul 22 '21 at 10:14
I can't provide a sample unfortunately, i can say columns/rows on sheet 3 and 4 are approx A1:AT5000 but can change everyday and I dont need to compare all columns, just upto the AC column. VLOOKUP would work but the number of products change everyday so thought running a macro would be better — partyyyy, Jul 22 '21 at 10:25
Not a solution but a note: `Dim LRow, LRow1 As Long` only declares `LRow1 As Long` but `LRow As Variant`. In VBA you need to specify a type for **every** variable or it is `Variant` by default: `Dim LRow As Long, LRow1 As Long`. Also you need to `Dim i As Long, r As Long` because you use them with `LRow` and therefore they need to be the same type. • You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — Pᴇʜ, Jul 22 '21 at 11:06

score 0 · Accepted Answer · edited Jul 24 '21 at 05:44

Please make adjustments as Peh says in upper comment.

For first part of your task (match/no match). You don't have to iterate second time, so simplify your VBA with

LRow = Sheets(4).Cells(Sheets(4).Rows.Count, "A").End(xlUp).Row
r = 4
For i = 2 To LRow
        If Sheets(4).Range("A" & i).Value = Sheets(3).Range("A" & i).Value Then
            Sheets(2).Range("D" & r).Value = "MATCH"
            r = r + 1
        Else
            Sheets(2).Range("D" & r).Value = "NO MATCH"
        End If
Next i

For second part of your task to find errors, you can iterate though each row in each column in search for all errors :).

Idea would be to find last right column and for i = 4 (as row "D") to last column find last row in this column and for row = 2 to last_row_of_selected_column find all errors if yes then add+1 or storage it some other way.

Please also consider adding at start of code

Application.Screenupdating = False

At end of the code

Application.Screenupdating = True

Compare multiple columns on 2 seperate sheets and says "Match" or "No Match?

1 Answers1