compare column A in WS1 with Column A in WS2. If match then copy to WS3. If no match then copy to "NotFound".

nguy0279

New Member
Joined
Jul 29, 2014
Messages
5
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
 
nguy0279,

Thanks for the help.

You are very welcome.

Sorry that I was not able to solve your changing requests.


Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.
 
Upvote 0

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Forum statistics

Threads
1,223,250
Messages
6,171,036
Members
452,374
Latest member
keccles

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top