Hi,

Am Tue, 02 Sep 2014 19:53:55 +1000 schrieb XR8 Sprintless:

Data sheet column D contains the entry 123456ABC

Errata sheet column A row 3 contains 123456
Errata sheet column C row 3 contains XYZABC12

If the partial match is found I want the value in column D whatever row
it is in to now equal XYZABC12

So on a bigger scale

DATA_D ERRATA_A ERRATA_C
123abc 123 XYZ132
234abc 234a XZY495
678bde 132q PSU091
132qwe 678b ddeeff

Should have column D become
XYZ132
XZY495
ddeeff
PSU091

try:

Sub ReplaceMatches()

Dim shtOld As Worksheet, shtNew As Worksheet
Dim i As Integer, f As Range
Dim LRowD As Long, LRowE As Long
Dim FirstAddress As String

Set shtOld = ThisWorkbook.Sheets("Errata")
Set shtNew = ThisWorkbook.Sheets("Data")

With shtOld
LRowE = .Cells(Rows.Count, 1).End(xlUp).Row
LRowD = shtNew.Cells(Rows.Count, 4).End(xlUp).Row
For i = 2 To LRowE
Set f = shtNew.Range("D2:D" & LRowD).Find(.Cells(i, 1), _
LookIn:=xlValues, lookat:=xlPart)
If Not f Is Nothing Then
FirstAddress = f.Address
Do
shtNew.Cells(f.Row, 4) = .Cells(i, 3)
Set f = shtNew.Range("D2:D" & LRowD).FindNext(f)
Loop While Not f Is Nothing And f.Address < FirstAddress
End If
Next i
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional