trivrain
03-16-2011, 03:09 PM
Hello,
I am trying to achieve the below:
With 2 excel spreads T1 and T2
I want to compare the FULL content of column B (1st name), C (last name), & E (company) in T1 to the content of A (1st name), B (last name), D (company) in T2 for each records.
If for a record all above cells match, copy the content of T1's column J in T2's column M.
If not all match highlight the record in red in T1 .
Can anyone help ?
Could be based on the below previous code from XLD
Public Sub ProcessData()
Dim targetWb As Workbook
Dim targetWs As Worksheet
Dim Lastrow As Long
Dim Matchrow As Long
Dim i As Long
Application.ScreenUpdating = False
Set targetWb = Workbooks("T2.xls")
Set targetWs = targetWb.Worksheets(1)
With Workbooks("T1.xls").Worksheets(1)
Lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = 2 To Lastrow
Matchrow = 0
On Error Resume Next
Matchrow = Application.Match(.Cells(i, "B").Value2, targetWs.Columns("A"), 0)
On Error GoTo 0
If Matchrow > 0 Then
.Cells(i, "R").Value2 = targetWs.Cells(Matchrow, "Q").Value2
End If
Next i
End With
Set targetWs = Nothing
Set targetWb = Nothing
Application.ScreenUpdating = True
End Sub
I am trying to achieve the below:
With 2 excel spreads T1 and T2
I want to compare the FULL content of column B (1st name), C (last name), & E (company) in T1 to the content of A (1st name), B (last name), D (company) in T2 for each records.
If for a record all above cells match, copy the content of T1's column J in T2's column M.
If not all match highlight the record in red in T1 .
Can anyone help ?
Could be based on the below previous code from XLD
Public Sub ProcessData()
Dim targetWb As Workbook
Dim targetWs As Worksheet
Dim Lastrow As Long
Dim Matchrow As Long
Dim i As Long
Application.ScreenUpdating = False
Set targetWb = Workbooks("T2.xls")
Set targetWs = targetWb.Worksheets(1)
With Workbooks("T1.xls").Worksheets(1)
Lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = 2 To Lastrow
Matchrow = 0
On Error Resume Next
Matchrow = Application.Match(.Cells(i, "B").Value2, targetWs.Columns("A"), 0)
On Error GoTo 0
If Matchrow > 0 Then
.Cells(i, "R").Value2 = targetWs.Cells(Matchrow, "Q").Value2
End If
Next i
End With
Set targetWs = Nothing
Set targetWb = Nothing
Application.ScreenUpdating = True
End Sub