Find and replace--Sort Of

[Joe]

I have a work sheet with data in columns B and C. I am wondering if there is
an automated way in which I can search for a certain value in Column B and
based upon that field change the data in Column C? As in the sample below
Column C data is not the same for the data listed in Column B, I need the
macro or formula to search Column B for 4042 and change all entries in
Column C to 25013, the same would be for 4054 and replace data in Column C
to 25011. Any help would be appreciated.

Column
B C
Manufact Part Number
4042 25013
4042 25014
4054 25011
4054 25011
4054 25011
4054 25011
4054 25013
4054 25021

 

[Bernie Deitrick]

Joe,

1) Manually, you would filter the list to show only 4042s in column B, then
select the data area of column C and use Edit /Go to... Special Visible
Cells only, then type in 25013 and press Ctrl-Enter.

2) With formulas, you would set up a table, say in E2:F4
4042 25013
4054 25011
8888 33333

In Cell C2, you would use =VLOOKUP(B2,$E$2:$F$4,2,False)
And copy down to match column B. Then copy the formulas and pastespecial
values to remove the formulas.

3) In a macro, you would use

Sub ReplaceSortOf()
Dim myC As Range
For Each myC In Range(Range("B2"),Range("B" & Rows.Count).End(xlUp))
If myC.Value = 4042 Then myC(1,2).Value = 25013
If myC.Value = 4054 Then myC(1,2).Value = 25011
If myC.Value = 8888 Then myC(1,2).Value = 33333
Next myC
End Sub

I hope you see the pattern with the If then statements.... You could also
use Select Case statements, but that is probably not needed for a one-off,
and it is a little more complicated...

HTH,
Bernie
MS Excel MVP

 

[Joe]

This work perfect. I would like to say thank you for the assistance because
done manually this process was taking some of our folk two hours per month.
This will be a nice little time saver.