Excel VBA to cross match 2 sheets data and output into sheet3

[satish78]

Hi Friends,

Trying to match 2 sheets data and copy entire rows into sheet3
I been through posts on this mrexcel forum and on google search but did not find a correct vba to do the task.

In sheet1 ColumnB is always blank
In sheet2 ColumnE is always blank

I manually copied what results should look in sheet3 for clear understanding what I am trying to cross check data.

Sample file attached

https://spaces.hightail.com/space/sttiYg9isR

Satish

 

[DanteAmor]

If you can't remove those strange characters from the sheet, then


Try this

[COLOR=#333333]Sub cross_match_2_sheets()[/COLOR]
[COLOR=#333333]Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet[/COLOR]
[COLOR=#333333]Dim f As Range, lr As Long, i As Long
[/COLOR]On error Resume Next
[COLOR=#333333]Set sh1 = Sheets("Sheet5")[/COLOR]
[COLOR=#333333]Set sh2 = Sheets("Sheet6")[/COLOR]
[COLOR=#333333]Set sh3 = Sheets("Sheet8")[/COLOR]
[COLOR=#333333]For i = 1 To sh1.Range("D" & Rows.Count).End(xlUp).Row[/COLOR]
[COLOR=#333333]Set f = sh2.Range("B:B").Find(sh1.Cells(i, "D").Value, , xlValues, xlWhole)[/COLOR]
[COLOR=#333333]If Not f Is Nothing Then[/COLOR]
[COLOR=#333333]lr = sh3.Range("E" & Rows.Count).End(xlUp).Row + 1[/COLOR]
[COLOR=#333333]sh3.Range("A" & lr).Value = sh2.Range("C" & i).Value[/COLOR]
[COLOR=#333333]sh3.Range("B" & lr).Value = sh2.Range("D" & i).Value[/COLOR]
[COLOR=#333333]sh3.Range("C" & lr).Value = sh1.Range("C" & i).Value[/COLOR]
[COLOR=#333333]sh3.Range("D" & lr).Value = sh2.Range("F" & i).Value[/COLOR]
[COLOR=#333333]sh3.Range("E" & lr).Value = sh1.Range("D" & i).Value[/COLOR]
[COLOR=#333333]End If[/COLOR]
[COLOR=#333333]Next[/COLOR]
[COLOR=#333333]MsgBox "End"[/COLOR]
[COLOR=#333333]End Sub[/COLOR]

 

[DanteAmor]

In your file column D of sheet1 is empty.

Organize your sheet and your examples well, when you are clear, let me know, and I will gladly help you.

 

[satish78]

Yes, That is true. ColumnD is empty on sheet1 and ColumnC is empty on sheet2

 

[DanteAmor]

Then upload another file with real data to the cloud where the data is seen as it is in reality.