Find a value in a row then change another in same row

[scrupul0us]

I need a basic replacement that will search column C for a string.. if it exists change a value in column L of that same row...

i have about 5 or 6 strings (full proper personell names) to look for and they can appear up to about 10-20 times

any help would be appreciated... thanks

 

[NBVC]

My example changes occurances of "manual" within strings in col A to whatever I have in column E:

Formula in Col B: =SUBSTITUTE(A1:A3,"manual",E1:E3)

Evaluated with Ctrl+Shift+Enter, not just Enter. And copied down.

Book2
	A	B	C	D	E
1	jon - manual	jon - auto			auto
2	jack - auto	jack - auto			manual
3	marc - manual	marc - auto			auto
Sheet2

 

[scrupul0us]

not exactlw hat im looking for... this is what im delaing with

lets say in column A I have the name:

"smith, jon" and it appears 10 times

in column B is a dept value for that name... however he has 2 departments listed

i wanna be able to look for someones name in one column and manually set their department in another column

i have a few names like this that need to be looked for and have their departments changed (the departments arent the same)

even if its a snippet marco that i have to run multiple times for multiple names thats fine

 

[dgpenny]

It is always helpful to show what you want the results to be, not just describe them. Before and after is the best (for me anyway).

 

[scrupul0us]

_Column A_|_Column B_

Jon, Smith | 20
Jon, Smith | 20
Jon, Smith | 20
Jon, Smith | 20
Jon, Smith | 21
Jon, Smith | 22
Jon, Smith | 20
Doe, smith | 19
Doe, smith | 19
Doe, smith | 19
Doe, smith | 18
Doe, smith | 20

Now say I need all Jon, Smith's to be dept 22 and doe, smith's to be dept 16 instead of whats there.. i need to search for their name and then make the replacement in the dept column based on the new depts i know the are to be placed in

since im at home ATM i do not know the exact columns for name & dept but i do know they arent side by side

but out of my list of about 200 names i have 4 that need this kind of change

 

[NBVC]

Start by creating a small table on the side, listing the Names and Assigned Depts.

Now, If this is the full list and no more are to be added, then you can simply overwrite the current data in the department column with a vlookup formula.

Or you can add a helper column next to the current department column with the vlookup formula. Then you can simply hide the old column from view.

Formula: =VLOOKUP(A1,$E$1:$F$2,2,0) where A1 is the name to lookup, E1:F2 is the table range (you can replace this with a named reference, such as Table), the 2 is the column number in the table to extract the info from and the 0 looks for an exact match.

 

[scrupul0us]

unfotunately that isnt the kind of solution that i require... while that does work it needs to be a small macro solution todo this as the sheet length nears about 55,000 lines... cannot use helper columns, pull down formulas etc since it will bog down the overall macro way too much...

i know someone like nateo can help me out here

thanks for the assitance thus far NBVC

 

[just_jon]

Wondering if an automated filter, after prompting for the name to key on, then use SpecialCells(xlCellTypeVisible) might be a quick method.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> foo()
<SPAN style="color:#00007F">Dim</SPAN> Tgt <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, RepWith
Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
Tgt = Application.InputBox("Enter Target")
RepWith = Application.InputBox("Enter replacement value")
<SPAN style="color:#00007F">If</SPAN> [C:C].AutoFilter = <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#00007F">Then</SPAN>
    [C:C].AutoFilter
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
[C:C].AutoFilter Field:=1, Criteria1:=Tgt
<SPAN style="color:#00007F">With</SPAN> Range("L2:L" & Cells(Rows.Count, "L").End(xlUp).Row).SpecialCells(xlCellTypeVisible)
    .Value = RepWith
<SPAN style="color:#00007F">End</SPAN> With
[C:C].AutoFilter
Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

UNTESTED...

 

[scrupul0us]

illl just have to test this with hard coded values later today

 

[scrupul0us]

that works great... thank you very much!