Find and replace script / macro?

Help Needed: Find and replace script / macro?

Hi,

I'm not sure what type of function is needed for what I'm describing below. I'm thinking it is a script or macro? I don't know anything about writing scripts or macros and I am quite new to excel. I'm hoping someone can help.

I'm trying to learn chinese and am making chinese flashcards for myself.

The fact that some of the cells have chinese characters probably doesn't matter, but I just want to be thorough in my description.



Example:
A---------------B---------------C
dog-------------gǒu-------------The dog is in the house. Na ge gǒu zai jiālǐ.
家裡-------------jiālǐ-------------The dog is in the 家裡. Na ge gǒu zai jiālǐ.

I need for the script to take the value of Cell in Column A "dog" and the value of Cell in Column B "gǒu" and replace those with " ~ " in Cell C.

Thus, after running the script/macro the output will be:
A---------------B---------------C
dog-------------gǒu-------------The ~ is in the house. Na ge ~ zai jiālǐ.
家裡-------------jiālǐ-------------The dog is in the ~ . Na ge gǒu zai ~ .



I will be eternally grateful for help on this!!


P.S. - the formatting for the tabs look strange (i used ---- instead). I'm also attaching a sample of the excel file so that it can be read more easily.

Perhaps this will suffice:

Sub Find_N_Replace_All()
Dim Rng As Range, MyCell As Range
Set Rng = Range("A2:B" & Range("B" & Rows.Count).End(xlUp).Row)
For Each MyCell In Rng
   Columns(3).Replace What:=MyCell, Replacement:="~", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Next MyCell
End Sub

Well caught shg! i assumed that the user was going to have a complete list in column A & B but good thinking!

By the way i forgot about blanks with my "blanket" code so:

Sub Find_N_Replace_All()
Dim Rng As Range, MyCell As Range
Set Rng = Range("A2:B" & Range("B" & Rows.Count).End(xlUp).Row)
For Each MyCell In Rng
if mycell.value="" then GoTo Nxt
   Columns(3).Replace What:=MyCell, Replacement:="~", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Nxt:
Next MyCell
End Sub

Hold Alt and press F11, on the left hand side you see your worksheet names and ThisWorkbook right click there and choose INSERT, MODULE and paste thye code in, you can then run it from the worksheet by selecting VIEW in the menubar then on the left MACROS then choose the macro you want to run.

I think the problem is solved.

The final code below appears to work correctly!:

Sub Find_N_Replace_All()
Dim Rng As Range, MyCell As Range
Set Rng = Range("A2:B" & Range("B" & Rows.Count).End(xlUp).Row)
For Each MyCell In Rng
If MyCell.Value = "" Then GoTo Nxt
   Cells(MyCell.Row, "C").Replace What:=MyCell.Value, Replacement:="~", _
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
        SearchFormat:=False, ReplaceFormat:=False
Nxt:
Next MyCell
End Sub

Thank you so much for the help everyone!!

If you find that you still have problems then see this thread: http://www.excelforum.com/excel-prog...character.html

Look towards the bottom to my post where you will find a Replace String function that you can manipulate and use. This has worked quite well for me on many projects.