Match and replace cell from another sheet

Question

I have no experience when it comes to this type of query and i'm stuck.

I've found a working query that i have used, here is the logic:

"If any cell in Sheet2 Column A = Any cell in Sheet1 Column B, change sheet2 column A cell value to sheet1 column A value"

The formula for this is:

Sub Test1()
Application.ScreenUpdating = False
Dim cell As Range, varFind As Variant
With Sheets("Sheet1")
For Each cell In Sheets("Sheet2").Columns(1).SpecialCells(2)
Set varFind = .Columns(2).Find(What:=cell.Value, LookIn:=xlFormulas, LookAt:=xlWhole)
If Not varFind Is Nothing Then cell.Value = .Cells(varFind.Row, 1).Value
Set varFind = Nothing
Next cell
End With
Application.ScreenUpdating = True
End Sub

That works fine for me, but I need it slightly different. It needs to follow this logic:

"If any cell in Sheet2 Column A = Any cell in Sheet1 Column B, change sheet1 column A cell value to sheet2 column B value"

Can anyone help please?

brettdj brettdj · Accepted Answer · 2011-10-28T11:02:46

[updated as I had needed to change the column on sheet 2 from A to B]

As you have writen this you would simply change this line

If Not varFind Is Nothing Then cell.Value = .Cells(varFind.Row, 1).Value

to

If Not varFind Is Nothing Then .Cells(varFind.Row, 1).Value = cell.Offset(0,1).Value

There is scope to improve this code speed if performance is an issue. Plus you will have a potential code bust if there are no constant cells in sheet 2 column B for SpecialCells to look at .... pls advise if you'd like the code tweaked further

Match and replace cell from another sheet

1 Answers