How can I preserve the text string variable in a macro?

I use the attached code as part of a much larger macro.
Cell M8 is formatted as text but contains a 6 digit number sequence starting "0000", eg 000021
When the code is run, the word "DEPOSIT" is replaced, but the result in that cell is "21"
Range A14:A25 is formatted as text.
How can I preserve the string please?
Sub Macro1()
Dim DocType As String
DocType = Range("M8").Value
With Range("A14:A25")
.Replace What:="DEPOSIT", Replacement:=DocType, LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        End With
End Sub

Open in new window

spar-kleOperations DirectorAsked:
Who is Participating?
 
Rory ArchibaldConnect With a Mentor Commented:
Or use an apostrophe:
DocType = "'" & Range("M8").Value
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Try:

DocType = cstr(Range("M8").Value)

Chris
0
 
spar-kleOperations DirectorAuthor Commented:
Excellent, thanks for your time.
Chris, your code returned "21"
Thanks anyway
Des
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.