Find values in one list and replace them with values from another list

[auto.pilot]

I have up to 1,000 entries (column A) on my Output worksheet. Some of them (100 or less) are in bad format or simply wrong. In a separate table on my Lookup worksheet, column A is populated with 'bad' entries that match the bad entries on the Output worksheet. Column B is populated with 'good' entries.

When working with smaller data sets and only one bad data point, I have used the following bit of code, which references the Output worksheet, column A. I know this is not good practice, since it can't be easily scaled.

On Error Resume Next
    For Each Rng In Range("A4:A" & La)
        If Rng.Value = "6070000000" Then
            Rng.Value = "'6071143E3"
        End If
    Next

I am seeking a VBA method to replace the bad entries on my Output worksheet based on columns A & B in my Lookup table.

How would I do this?

Thanks in advance for all replies.

Jim

 

[Fluff]

Re: Seeking method to find values in one list and replace them with values from another list

How about

Sub Mylookup()
   Dim cl As Range
   Dim Dic As Object
   
   Set Dic = CreateObject("scripting.dictionary")
   With Sheets("lookup")
      For Each cl In .Range("A1", .Range("A" & Rows.Count).End(xlUp))
         Dic(cl.Value) = cl.Offset(, 1).Value
      Next cl
   End With
   With Sheets("output")
      For Each cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
         If Dic.exists(cl.Value) Then cl.Value = Dic(cl.Value)
      Next cl
   End With
End Sub

 

[auto.pilot]

Re: Seeking method to find values in one list and replace them with values from another list

How about

Sub Mylookup()
   Dim cl As Range
   Dim Dic As Object
   
   Set Dic = CreateObject("scripting.dictionary")
   With Sheets("lookup")
      For Each cl In .Range("A1", .Range("A" & Rows.Count).End(xlUp))
         Dic(cl.Value) = cl.Offset(, 1).Value
      Next cl
   End With
   With Sheets("output")
      For Each cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
         If Dic.exists(cl.Value) Then cl.Value = Dic(cl.Value)
      Next cl
   End With
End Sub

Perfect!

Thanks for your prompt and excellent reply.

Jim

 

[Fluff]

Re: Seeking method to find values in one list and replace them with values from another list

Glad to help & thanks for the feedback