Excel - Find and replace using data from another sheet

[laquer09]

Alright, I'm trying to get a start on this one, and I know there are multiple methods. I have a workbook in which one sheet holds columns of data, one of which is acct number. When this sheet is originally populated, some of the acct numbers are wrong, and need to be changed. I want to add a sheet which will have two columns, Erred Acct and Correct Acct. I then want to write a macro that will find the erred acct and replace it with the correct acct (taken from the 2nd sheet). Any suggestions?

 

[luceze]

Name your erracct range RngErr
Name your AcctCode range RngErr

Then insert this in a module

Sub CorrectAccounts()
Dim cell1 As Range
Dim cell2 As Range
Dim errcode As String
Dim corrcode As String
For Each cell1 In Range("RngErr&quot
errcode = cell1
corrcode = cell1.Offset(0, 1)
For Each cell2 In Range("rngAcct&quot
If cell2 = errcode Then
cell2 = corrcode
End If
Next
Next
End Sub

HTH,
Eric

 

[luceze]

Sorry name your Acct number range RngAcct.

 

[sawedoff]

Say this is the first sheet
12345
12346
12357
123432
12123
42211
23442
534223

and this is the second (note they are sorted ascending)
12123 12349
12345 12345
12346 12346
12357 12347
23442 23442
42211 12350
123432 12348
534223 23443

The best way to correct sheet one would be to insert a column next to the incorrect accounts, and putthe following formula:
=VLOOKUP(A1,Sheet2!$A$1:$B$8,2)
into A2, and copy down.

It will put all the correct accounts into column 2. Then you can do a copy, paste values into column A and delete column B.