PDA

View Full Version : Solved: Loop find and replaced



khalid79m
03-26-2010, 09:27 AM
Wkst.Range("D2:D" & xRow).Replace What:="London", Replacement:="Mexico", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False


I have list of locations on sheets1 from "a2:a" & lastrow, next to these locations in column "b2:b" & lastrow I have reserve locations for example:

A2 = Leeds B2 = Manchester.

I need a code to start at a2 , and look for the value of a2 in sheets2.Range("D2:D" & xRow). and replace it with value b2 and keep doining it until it hits a blank in column a on sheet1

Can anyone help with this loop find and replace

Bob Phillips
03-26-2010, 10:03 AM
Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim sh As Worksheet
Dim cell As Range

Set sh = Worksheets("Sheet2")

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To LastRow

Set cell = Nothing
On Error Resume Next
Set cell = sh.Columns("D").Find(.Cells(i, "A").Value)
On Error GoTo 0
If Not cell Is Nothing Then cell.Value = .Cells(i, "B").Value
Next i
End With

End Sub

khalid79m
04-13-2010, 03:57 AM
thanks xld, works fine