Excel VBA find and replace

[jimbojames136]

Hi all, i have used some macro code to perform a find and replace on a formula in a table. (see below) i keep getting the " we couldnt find anything to replace" error box even though it does replace part of the formula. Im also aware that this probably isnt the cleanest way of doing it? i call this sub from another sheet that uses a listbox

 

Sub English()

'

' English Macro

' Changes Data to English

'

' Keyboard Shortcut: Ctrl+Shift+W

'

 

Range("C3:L21").Select

Range("L21").Activate

Selection.Replace What:="$L$6:$L$500", Replacement:="$K$6:$K$500", LookAt _

:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

ReplaceFormat:=False

Selection.Replace What:="$M$6:$M$500", Replacement:="$K$6:$K$500", LookAt _

:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

ReplaceFormat:=False

Selection.Replace What:="$N$6:$N$500", Replacement:="$K$6:$K$500", LookAt _

:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

ReplaceFormat:=False

Selection.Replace What:="$O$6:$O$500", Replacement:="$K$6:$K$500", LookAt _

:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

ReplaceFormat:=False

Selection.Replace What:="$P$6:$P$500", Replacement:="$K$6:$K$500", LookAt _

:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

ReplaceFormat:=False

Selection.Replace What:="$Q$6:$Q$500", Replacement:="$K$6:$K$500", LookAt _

:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

ReplaceFormat:=False

Selection.Replace What:="$R$6:$R$500", Replacement:="$K$6:$K$500", LookAt _

:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

ReplaceFormat:=False

Selection.Replace What:="$S$6:$S$500", Replacement:="$K$6:$K$500", LookAt _

:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

ReplaceFormat:=False

Selection.Replace What:="$T$6:$T$500", Replacement:="$K$6:$K$500", LookAt _

:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

ReplaceFormat:=False

Selection.Replace What:="$U$6:$U$500", Replacement:="$K$6:$K$500", LookAt _

:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

ReplaceFormat:=False

 

End Sub

 

 

thanks

 

James