Find Value and replace with value in cell A:1

[trenzalore888]

Hello! I am new to VBA and I would appreciate some help, I have been searching online but cant find a solution to my problem.

I currently have a working VBA code which finds and replaces the word "Business" with "Manchester United FC":


Worksheets("Sheet1").Columns("J").Replace _
What:="Business", Replacement:="Manchester United FC", _
SearchOrder:=xlByColumns, MatchCase:=True


But what I really want is Excel to get thereplacing value "Manchester United FC" from cell A:1, that way anytime someone changes A:1 to a different business, I wont have to go in and change the macro code.

I have tried:



Worksheets("Sheet1").Columns("J").Replace _
What:="Business", Replacement:= Range.("A:1").select _
SearchOrder:=xlByColumns, MatchCase:=True




But it does not work. Does anyone have a way around this? Thank you!

[dflak]

Try this:
What:="Business", Replacement:= Range.("A1")

[Richard Buttrey]

Hi,

Use a variable. i.e.

Please Login or Register  to view this content.

[trenzalore888]

Thank you so much, it works perfectly!