Macro to find and replace data from one sheet to another

[PostIt]

Hello,

I need to find and replace data (A2:B632) from Sheet 2 to Sheet 1, but I only want to replace the data for Column F to Column AM.
So that if the name in Column A of Sheet 2 matches any name in Sheet 1 (Column F - Column AM), the cell will be replaced with the cost which is given in Column B.
Is there a macro for that?

Or will I have to input separate Find and Replace macros?

Please advise.
Thank you!

Running:
Windows 7 Home Premium
Microsoft Office Home and Student 2010

 

[LloydFinancials]

Hello PostIt,

You can give this a try:

Sub findReplace()
  Dim i As Long
  Dim c As Range
  Dim r As Range
  On Error Resume Next
  For i = 2 To Worksheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
    Set c = Worksheets("Sheet2").Range("A" & i)
    Worksheets("Sheet1").Select
    Worksheets("Sheet1").Range("F:AM").Select
    Selection.Replace What:=c.Value, Replacement:=c.Offset(, 1), LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False
  Next i
  Worksheets("Sheet2").Select
  Worksheets("Sheet2").Range("A1").Select
End Sub

Luke

 

[PostIt]

Brilliant.
I don't know what it is, but this macro seems to be taking a really long time.
So far, the replacements seem to be going smoothly though.
Thank you!

 

[LloydFinancials]

Thank you for the feedback. Is there any way to update the range F:AM to something like F1:AM9999. Decreasing this range should help.

 

[PostIt]

I'm pretty sure there is but I'm not exactly super excel savvy, but I will look into it.
Thank you.