VBA - replace single character in worksheet

[jag108]

I am trying to import a text file into an application that send data to a Television set top box, this file iscreated using Excel. Part if the creation process needs to replace specific characters.

lrow = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
lcol = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column

Set rng = Range(Cells(5, 1), Cells(lrow, lcol))
rng.Select
With Selection
    .Replace What:="'", Replacement:="’", LookAt:=xlWhole, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End With

I am trying to replace ' with ’ as you can see they are different but the replace code I am trying to use does not seem to want to work for me.

 

[XLGibbs]

This works for me..

lRow = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
lcol = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column

Dim rng As Range

Set rng = Range(Cells(5, 1), Cells(lRow, lcol))
rng.Replace What:="'", Replacement:="’", LookAt:=xlPart