I have been trying to finish this macro. I have two sheets that will compare column A in WS1 with Column A in WS2. If match then copy to WS3. If no match then copy to "NotFound". The script works when the to columns equal each other. However, when the two columns do not match it will copy multiple times into the "NotFound" sheet. I understand that it is because of the loop, in that every time it runs through the code it will find the Non-matched value again. Hops this made sense. I have included the code below. I am new to VB so could use the help. Thank you.
WS 1
[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD][TABLE="width: 119"]
<colgroup><col></colgroup><tbody>[TR]
[TD]9781847320[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 159"]
<colgroup><col></colgroup><tbody>[TR]
[TD]989[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 119"]
<colgroup><col></colgroup><tbody>[TR]
[TD]9781780971[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 159"]
<colgroup><col></colgroup><tbody>[TR]
[TD]6561[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 119"]
<colgroup><col></colgroup><tbody>[TR]
[TD]978162772[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 159"]
<colgroup><col></colgroup><tbody>[TR]
[TD]CNVSR-C[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
WS2
[TABLE="width: 250"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD][TABLE="width: 153"]
<colgroup><col></colgroup><tbody>[TR]
[TD]4560220550[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1111[/TD]
[/TR]
[TR]
[TD]9781780971[/TD]
[TD]MPE6561[/TD]
[/TR]
[TR]
[TD]9781847322[/TD]
[TD]MBO989[/TD]
[/TR]
[TR]
[TD]4560220553[/TD]
[TD]1111[/TD]
[/TR]
</tbody>[/TABLE]
"NotFound" WS
[TABLE="width: 250"]
<tbody>[TR]
[TD]4560220553[/TD]
[TD]1111
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 250"]
<tbody>[TR]
[TD]4560220553[/TD]
[TD]1111[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 250"]
<tbody>[TR]
[TD]4560220553[/TD]
[TD]1111[/TD]
[/TR]
</tbody>[/TABLE]
Dim numRowsN As Long
Dim counterOriginal As Integer
Dim counterN As Integer
Dim counterSheet3 As Integer
Dim OriginalValue As String
Dim NValue As String
Dim counterNotFound As Integer
counterSheet3 = 2
Sheets("Sheet3").Columns("A").NumberFormat = "@"
numRowsOriginal = ActiveWorkbook.Worksheets("WS1").Range("A2",Worksheets("WS1").Range("A2").End(xlDown)).Rows.Count
numRowsN=ActiveWorkbook.Worksheets("WS2").Range("A2",Worksheets("WS2").Range("A2").End(xlDown)).Rows.Count
Sheets("WS1").Rows(1).Copy Sheets("Sheet3").Rows(1)
For counterOriginal = 2 To numRowsOriginal
For counterN = 2 To numRowsN + 1
OriginalValue = Sheets("WS1").Cells(counterOriginal, 1).Value
NValue = Sheets("N").Cells(counterN, 1).Value
If OriginalValue = NalpacValue Then
Sheets("WS1l").Rows(counterOriginal).Copy Sheets("Sheet3").Rows(counterSheet3)
'copy Manufacturer number
Sheets("WS2").Cells(counterN, "E").Copy Sheets("Sheet3").Cells(counterSheet3, "FG")
'copy N item number
Sheets("WS2").Cells(counterN, "B").Copy Sheets("Sheet3").Cells(counterSheet3, "FH")
'copy N price
Sheets("WS2").Cells(counterN, "G").Copy Sheets("Sheet3").Cells(counterSheet3, "FI")
'copy Qty
Sheets("WS2").Cells(counterN, "K").Copy Sheets("Sheet3").Cells(counterSheet3, "FJ")
counterSheet3 = counterSheet3 + 1
' ElseIf OriginaValue <> NValue Then
' Sheets("WS1").Rows(counterOriginal).Copy Sheets("NotFound").Rows(counterSheet3)
' counterNotFouind = counterNotFound + 1
End If
Next
Next
End Sub
WS 1
[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD][TABLE="width: 119"]
<colgroup><col></colgroup><tbody>[TR]
[TD]9781847320[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 159"]
<colgroup><col></colgroup><tbody>[TR]
[TD]989[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 119"]
<colgroup><col></colgroup><tbody>[TR]
[TD]9781780971[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 159"]
<colgroup><col></colgroup><tbody>[TR]
[TD]6561[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 119"]
<colgroup><col></colgroup><tbody>[TR]
[TD]978162772[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 159"]
<colgroup><col></colgroup><tbody>[TR]
[TD]CNVSR-C[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
WS2
[TABLE="width: 250"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD][TABLE="width: 153"]
<colgroup><col></colgroup><tbody>[TR]
[TD]4560220550[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1111[/TD]
[/TR]
[TR]
[TD]9781780971[/TD]
[TD]MPE6561[/TD]
[/TR]
[TR]
[TD]9781847322[/TD]
[TD]MBO989[/TD]
[/TR]
[TR]
[TD]4560220553[/TD]
[TD]1111[/TD]
[/TR]
</tbody>[/TABLE]
"NotFound" WS
[TABLE="width: 250"]
<tbody>[TR]
[TD]4560220553[/TD]
[TD]1111
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 250"]
<tbody>[TR]
[TD]4560220553[/TD]
[TD]1111[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 250"]
<tbody>[TR]
[TD]4560220553[/TD]
[TD]1111[/TD]
[/TR]
</tbody>[/TABLE]
Dim numRowsN As Long
Dim counterOriginal As Integer
Dim counterN As Integer
Dim counterSheet3 As Integer
Dim OriginalValue As String
Dim NValue As String
Dim counterNotFound As Integer
counterSheet3 = 2
Sheets("Sheet3").Columns("A").NumberFormat = "@"
numRowsOriginal = ActiveWorkbook.Worksheets("WS1").Range("A2",Worksheets("WS1").Range("A2").End(xlDown)).Rows.Count
numRowsN=ActiveWorkbook.Worksheets("WS2").Range("A2",Worksheets("WS2").Range("A2").End(xlDown)).Rows.Count
Sheets("WS1").Rows(1).Copy Sheets("Sheet3").Rows(1)
For counterOriginal = 2 To numRowsOriginal
For counterN = 2 To numRowsN + 1
OriginalValue = Sheets("WS1").Cells(counterOriginal, 1).Value
NValue = Sheets("N").Cells(counterN, 1).Value
If OriginalValue = NalpacValue Then
Sheets("WS1l").Rows(counterOriginal).Copy Sheets("Sheet3").Rows(counterSheet3)
'copy Manufacturer number
Sheets("WS2").Cells(counterN, "E").Copy Sheets("Sheet3").Cells(counterSheet3, "FG")
'copy N item number
Sheets("WS2").Cells(counterN, "B").Copy Sheets("Sheet3").Cells(counterSheet3, "FH")
'copy N price
Sheets("WS2").Cells(counterN, "G").Copy Sheets("Sheet3").Cells(counterSheet3, "FI")
'copy Qty
Sheets("WS2").Cells(counterN, "K").Copy Sheets("Sheet3").Cells(counterSheet3, "FJ")
counterSheet3 = counterSheet3 + 1
' ElseIf OriginaValue <> NValue Then
' Sheets("WS1").Rows(counterOriginal).Copy Sheets("NotFound").Rows(counterSheet3)
' counterNotFouind = counterNotFound + 1
End If
Next
Next
End Sub