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

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" wrote in message
...
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

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.


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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" wrote in message
...
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