Find and replace with a lookup
 

I have two corresponding columns:

Column A
145431
145432
145433

Column B
DEFECT.1234
DEFECT.12351
DEFECT.1236

And another non-corresponding column that I want to modify with data from
Column A above:

Column A2
4645645DEFECT.1236131546
412312DEFECT.123487416
21168754DEFECT.1235155646

I want to search for a match between columns B and A2, and replace the
matching string within A2 with the corresponding value from A. Thanks in
advance.

Find and replace with a lookup
 

please explain col A2

"EMG03" wrote:

I have two corresponding columns:

Column A
145431
145432
145433

Column B
DEFECT.1234
DEFECT.12351
DEFECT.1236

And another non-corresponding column that I want to modify with data from
Column A above:

Column A2
4645645DEFECT.1236131546
412312DEFECT.123487416
21168754DEFECT.1235155646

I want to search for a match between columns B and A2, and replace the
matching string within A2 with the corresponding value from A. Thanks in
advance.

Find and replace with a lookup
 

Column A2 is simply a separate column, unrelated to A and B. In other words,
in a separate worksheet.

"Vacation's Over" wrote:

please explain col A2

"EMG03" wrote:

I have two corresponding columns:

Column A
145431
145432
145433

Column B
DEFECT.1234
DEFECT.12351
DEFECT.1236

And another non-corresponding column that I want to modify with data from
Column A above:

Column A2
4645645DEFECT.1236131546
412312DEFECT.123487416
21168754DEFECT.1235155646

I want to search for a match between columns B and A2, and replace the
matching string within A2 with the corresponding value from A. Thanks in
advance.

Find and replace with a lookup
 

I get it now

YOu want to use worksheet functions Find and Substitute

Air code check syntax

dim myCellB as range
dim myCellA2 as range
dim myRangeB as range
Dim myRangeA2 as range

set myRangeB =' setittocolB
set myRangeA2 = 'setittorangeA2

for each myCellB in myRangeB
for each myCellA2 in myrangeA2
if iserror(worksheetfunction.Find(myCellB ,myCellA2) then
next myCellA2
Else:
worksheetfunction.substitute(myCellA2, myCellB, myCellB.offset(0,-1))
end If
next myCellA2
Next myCellB

set MyRangeB = nothing
set myRangeA2 = Nothing

"EMG03" wrote:

Column A2 is simply a separate column, unrelated to A and B. In other words,
in a separate worksheet.

"Vacation's Over" wrote:

please explain col A2

"EMG03" wrote:

I have two corresponding columns:

Column A
145431
145432
145433

Column B
DEFECT.1234
DEFECT.12351
DEFECT.1236

And another non-corresponding column that I want to modify with data from
Column A above:

Column A2
4645645DEFECT.1236131546
412312DEFECT.123487416
21168754DEFECT.1235155646

I want to search for a match between columns B and A2, and replace the
matching string within A2 with the corresponding value from A. Thanks in
advance.

Find and replace with a lookup
 

Thanks very much for the help. I added the real ranges and also created a
third range for my "column A" since that is what I want to replace the string
with. However I can't get the code to work. Error comes back on the NEXT
statement inside the IF statement (says "Next without For") and there is also
a problem with the substitute function. Error says "Expected: =" Any help
is appreciated. My code below:

Dim myCellA As Range
Dim myCellB As Range
Dim myCellA2 As Range
Dim myRangeA As Range
Dim myRangeB As Range
Dim myRangeA2 As Range

Set myRangeA = Worksheets("Updates Required").Range("E2:E1270")
Set myRangeB = Worksheets("Updates Required").Range("F2:F1270")
Set myRangeA2 = Worksheets("Updates Required").Range("C2:C1524")

For Each myCellA In myRangeA
For Each myCellB In myRangeB
For Each myCellA2 In myRangeA2
If IsError(WorksheetFunction.Find(myCellB, myCellA2)) Then
Next myCellA2
Else
worksheetfunction.substitute(myCellA2, myCellA, myCellA.offset(0,-1))
End If
Next myCellA
Next myCellB
Next myCellA2

Set myRangeA = Nothing
Set myRangeB = Nothing
Set myRangeA2 = Nothing

End Sub

"Vacation's Over" wrote:

I get it now

YOu want to use worksheet functions Find and Substitute

Air code check syntax

dim myCellB as range
dim myCellA2 as range
dim myRangeB as range
Dim myRangeA2 as range

set myRangeB =' setittocolB
set myRangeA2 = 'setittorangeA2

for each myCellB in myRangeB
for each myCellA2 in myrangeA2
if iserror(worksheetfunction.Find(myCellB ,myCellA2) then
next myCellA2
Else:
worksheetfunction.substitute(myCellA2, myCellB, myCellB.offset(0,-1))
end If
next myCellA2
Next myCellB

set MyRangeB = nothing
set myRangeA2 = Nothing

"EMG03" wrote:

Column A2 is simply a separate column, unrelated to A and B. In other words,
in a separate worksheet.

"Vacation's Over" wrote:

please explain col A2

"EMG03" wrote:

I have two corresponding columns:

Column A
145431
145432
145433

Column B
DEFECT.1234
DEFECT.12351
DEFECT.1236

And another non-corresponding column that I want to modify with data from
Column A above:

Column A2
4645645DEFECT.1236131546
412312DEFECT.123487416
21168754DEFECT.1235155646

I want to search for a match between columns B and A2, and replace the
matching string within A2 with the corresponding value from A. Thanks in
advance.