Help with creating a macro...

[MyHero]

I have a list of items in column A. I need to replace all the occurances of the word tor1 with the value in b1. The value in B1 changes frequently.

Could someone help me write a macro that would accomplish this?

 

[silkfire]

=SUBSTITUTE(A1,"tor1",B1)

Replace red text with current value in your cell.

 

[MyHero]

=SUBSTITUTE(A1,"tor1",B1)

Replace red text with current value in your cell.

Incredible, thanks a lot man! How could I modify this formula to search all of column A?

 

[Smitty]

Welcome to the Board!

This one can be recorded using Find & Replace, then just replace the Replacement:="Sometext" with Replacement:=Range("B1").

    Range([A1], Cells(Rows.Count, "A").End(xlDown)).Replace What:="tor1", Replacement:=Range("B1").Value, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

Hope that helps,

 

[MyHero]

Welcome to the Board!

This one can be recorded using Find & Replace, then just replace the Replacement:="Sometext" with Replacement:=Range("B1").

    Range([A1], Cells(Rows.Count, "A").End(xlDown)).Replace What:="tor1", Replacement:=Range("B1").Value, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

Hope that helps,

Thanks a lot Smitty, you guys are incredible. My friends ask me for excel help, but when I need help, this is where I go! :D