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]

According to your example, should the columns Sheet1-colD be compared against Sheet2-colB?

 

[satish78]

Yes, Should compare.

 

[satish78]

According to your example, should the columns Sheet1-colD be compared against Sheet2-colB?

Yes, we have to compare Sheet1-colD be compared against Sheet2-colB.

 

[DanteAmor]

Try this

Sub cross_match_2_sheets()
  Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet
  Dim f As Range, lr As Long, i As Long
  Set sh1 = Sheets("Sheet1")
  Set sh2 = Sheets("Sheet2")
  Set sh3 = Sheets("Sheet3")
  For i = 1 To sh1.Range("D" & Rows.Count).End(xlUp).Row
    Set f = sh2.Range("B:B").Find(sh1.Cells(i, "D").Value, , xlValues, xlWhole)
    If Not f Is Nothing Then
      lr = sh3.Range("E" & Rows.Count).End(xlUp).Row + 1
      sh3.Range("A" & lr).Value = sh2.Range("C" & i).Value
      sh3.Range("B" & lr).Value = sh2.Range("D" & i).Value
      sh3.Range("C" & lr).Value = sh1.Range("C" & i).Value
      sh3.Range("D" & lr).Value = sh2.Range("F" & i).Value
      sh3.Range("E" & lr).Value = sh1.Range("D" & i).Value
    End If
  Next
  MsgBox "End"
End Sub

 

[satish78]

Try this

Sub cross_match_2_sheets()
  Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet
  Dim f As Range, lr As Long, i As Long
  Set sh1 = Sheets("Sheet1")
  Set sh2 = Sheets("Sheet2")
  Set sh3 = Sheets("Sheet3")
  For i = 1 To sh1.Range("D" & Rows.Count).End(xlUp).Row
    Set f = sh2.Range("B:B").Find(sh1.Cells(i, "D").Value, , xlValues, xlWhole)
    If Not f Is Nothing Then
      lr = sh3.Range("E" & Rows.Count).End(xlUp).Row + 1
      sh3.Range("A" & lr).Value = sh2.Range("C" & i).Value
      sh3.Range("B" & lr).Value = sh2.Range("D" & i).Value
      sh3.Range("C" & lr).Value = sh1.Range("C" & i).Value
      sh3.Range("D" & lr).Value = sh2.Range("F" & i).Value
      sh3.Range("E" & lr).Value = sh1.Range("D" & i).Value
    End If
  Next
  MsgBox "End"
End Sub

Here is the code which I modified according the data present in different sheets and I changed sheets name.
Please correct me. I am getting run time error at Set f = sh2.Range("B:B").Find(sh1.Cells(i, "D").Value, , xlValues, xlWhole)


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

 

[DanteAmor]

What does the error message say?

 

[satish78]

Run type error: '13':
Type mismatch

What does the error message say?

 

[DanteAmor]

What do you have on sheet "Sheet5" in cell D1?
Do you have a formula?
Do you have an error in the cell?

Is the cell combined?


The problem is data, the macro works.


You can try macro with the data you put in your sample file.

 

[satish78]

I copied that data into sample file.
It seems that some character is stopping to extract.
there are some weird characters like this

°ღ•ßŐŶÁ℘ÁŤℐ●•٠·˙


Is this stopping the script to run and complete.
And its not looping through all rows in sheet(s).

What do you have on sheet "Sheet5" in cell D1?
Do you have a formula?
Do you have an error in the cell?

Is the cell combined?


The problem is data, the macro works.


You can try macro with the data you put in your sample file.