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 !
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.