Hi
The problem is the program is going into an interminable loop.
By the time it gets to row 4 of sheet1 (when i=4), the values have
already been changed to USD.
Searching for US, finds USD and changes it to USD and so on ad
infinitum.
I have modified Bob's code to make it look at the whole of USD, not just
part of the value, which allows the routine to complete i=4 and move on
through the rest of its iterations.
Public Sub ProcessData2()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim cell As Range
Dim sh As Worksheet
Dim sFirst As String, Value1 As String, value2 As String
On Error Resume Next
With Worksheets("Sheet1")
iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To iLastRow
Value1 = Sheets("Sheet1").Cells(i, "A").Value
value2 = Sheets("Sheet1").Cells(i, "B").Value
With Worksheets("Sheet2")
Set cell = Nothing
Set cell = Worksheets("Sheet2").UsedRange _
.Find(What:=Value1, _
Lookat:=xlWhole, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)
If Not cell Is Nothing Then sFirst = cell.Address
Do
If Not cell Is Nothing Then
cell.Value = value2
Set cell =
Worksheets("Sheet2").UsedRange.FindNext(cell)
End If
Loop While Not cell Is Nothing
End With
Next i
End With
End Sub
My routine is a little longer than Bob's, as I have read the values from
Sheet1 into Value1 and Value2, whereas Bob was doing the whole job much
more concisely. I (being less experienced than Bob) find it easier to do
this so I can see the values clearly when I am stepping through the code
to see if I have got it right.
