PDA

View Full Version : Solved: Compare/replace based on equivalence list



trivrain
12-11-2010, 07:23 PM
Hi all,

I need to correct a column BM in worbook T1.xls, according to an equivalence list on 2 columns A & B in T2.xls structured as below:

A B
Wrong1 Corrected1
OK1 OK1
OK2 OK2
Wrong2 Corrected2

I am looking for a VBA code that will compare column BM in T1.xls to column A in T2.xls
and if, for example, cell BM7 in T1 matches cell A4 in T2 (Wrong2) > the content of T1's BM7 will automatically be replaced by the content of T2's A4+1column, that is B4 (Corrected2).

Am I making any sense ? :help

Any idea ? Many thanks !

Bob Phillips
12-12-2010, 02:56 AM
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, "A").End(xlUp).Row
For i = 2 To Lastrow

On Error Resume Next
Matchrow = Application.Match(.Cells(i, "BM").Value2, targetWs.Columns("A"), 0)
On Error GoTo 0
If Matchrow > 0 Then

.Cells(i, "BM").Value2 = targetWs.Cells(Matchrow, "B").Value2
End If
Next i
End With

Set targetWs = Nothing
Set targetWb = Nothing

Application.ScreenUpdating = True
End Sub

trivrain
12-12-2010, 06:14 AM
Thanks!

But I am afraid nothing is happening...
Alas my poor no lenge of VBA does not allow me to see where the process is failiing... : pray2:




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, "A").End(xlUp).Row
For i = 2 To Lastrow

On Error Resume Next
Matchrow = Application.Match(.Cells(i, "BM").Value2, targetWs.Columns("A"), 0)
On Error GoTo 0
If Matchrow > 0 Then

.Cells(i, "BM").Value2 = targetWs.Cells(Matchrow, "B").Value2
End If
Next i
End With

Set targetWs = Nothing
Set targetWb = Nothing

Application.ScreenUpdating = True
End Sub

Bob Phillips
12-12-2010, 10:19 AM
See if this works better



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("Book2") '"T2.xls")
Set targetWs = targetWb.Worksheets(1)

With Workbooks("Book1").Worksheets(1) 'Workbooks("T1.xls").Worksheets(1)

Lastrow = .Cells(.Rows.Count, "BM").End(xlUp).Row
For i = 2 To Lastrow

Matchrow = 0
On Error Resume Next
Matchrow = Application.Match(.Cells(i, "BM").Value2, targetWs.Columns("A"), 0)
On Error GoTo 0
If Matchrow > 0 Then

.Cells(i, "BM").Value2 = targetWs.Cells(Matchrow, "B").Value2
End If
Next i
End With

Set targetWs = Nothing
Set targetWb = Nothing

Application.ScreenUpdating = True
End Sub

trivrain
12-12-2010, 02:14 PM
Exceution error '9' :mkay
(translating from french)


See if this works better



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("Book2") '"T2.xls")
Set targetWs = targetWb.Worksheets(1)

With Workbooks("Book1").Worksheets(1) 'Workbooks("T1.xls").Worksheets(1)

Lastrow = .Cells(.Rows.Count, "BM").End(xlUp).Row
For i = 2 To Lastrow

Matchrow = 0
On Error Resume Next
Matchrow = Application.Match(.Cells(i, "BM").Value2, targetWs.Columns("A"), 0)
On Error GoTo 0
If Matchrow > 0 Then

.Cells(i, "BM").Value2 = targetWs.Cells(Matchrow, "B").Value2
End If
Next i
End With

Set targetWs = Nothing
Set targetWb = Nothing

Application.ScreenUpdating = True
End Sub

trivrain
12-12-2010, 02:16 PM
And if I go for debug, it highlights:
Set targetWb = Workbooks("Book2") '"T2.xls")


Exceution error '9' :mkay
(translating from french)

Simon Lloyd
12-12-2010, 03:00 PM
That error means that the workbook or worksheet cannot be found, what you should have done is changed xld's code to this:
Set targetWb = Workbooks("T2.xls")
Because i assume your workbook is called T2 and NOT Book2.

Dont forget to do the same with this lineWith Workbooks("Book1").Worksheets(1) 'Workbooks("T1.xls").Worksheets(1)

Bob Phillips
12-12-2010, 03:29 PM
Sorry, I forgot to adapt to your workbooks



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, "BM").End(xlUp).Row
For i = 2 To Lastrow

Matchrow = 0
On Error Resume Next
Matchrow = Application.Match(.Cells(i, "BM").Value2, targetWs.Columns("A"), 0)
On Error Goto 0
If Matchrow > 0 Then

.Cells(i, "BM").Value2 = targetWs.Cells(Matchrow, "B").Value2
End If
Next i
End With

Set targetWs = Nothing
Set targetWb = Nothing

Application.ScreenUpdating = True
End Sub

trivrain
12-12-2010, 03:37 PM
Thanks for all the precious help !
Will try it and confirm tomorrow.

trivrain
12-13-2010, 02:46 AM
Ah, back to sitution 1:
No error code, but nothing is happening...

trivrain
12-13-2010, 02:51 AM
Silly me ! It does work, I had not even noticed that my first spread was not actually in Woorksheet(1).

Thank you !