Excel VBA Replace not executing

Here is the working sub with some minor changes (look at the comments). I tried to stick to your original code as much as possible so you can find yourself in it. Furthermore, I implemented some good coding practices such as naming of variables and using .Value2 instead of .Value:

Option Explicit
Option Compare Text

Sub ReplaceTextCellByCell()

Dim shtData As Worksheet
Dim lngLastRow As Long
Dim rngAllData As Range, rngCell As Range

Set shtData = ThisWorkbook.Worksheets("StatementData")
lngLastRow = shtData.Cells(shtData.Rows.Count, "A").End(xlUp).Row

'I exchanged the comma for a colon. The comma would mean
'  that you are referring to two cells only. The cell
'  A1 and the cell K20 (or whatever the last row is)
'  The colon instead means that you want every cell
'  between these two to be included in the range
Set rngAllData = shtData.Range("A1:K" & lngLastRow)

'The following line is not necessary. Therefore I commented it out.
'shtData.Activate

For Each rngCell In rngAllData
    If InStr(1, rngCell.Value2, "Investor Ref :") Then
        rngCell.Value = Replace(rngCell.Value2, "Investor Ref :", "")
    End If
Next rngCell

End Sub

The following sub is a slight improvement over the first sub in terms of speed. Furthermore, the last row is now no longer determined based on column A only but instead on the last overall row. You can change this back again if you prefer.

Option Explicit
Option Compare Text

Sub ReplaceTextWithFind()

Dim shtData As Worksheet
Dim lngLastRow As Long
Dim rngAllData As Range, rngCell As Range, strFirstAddress As String

Set shtData = ThisWorkbook.Worksheets("StatementData")
lngLastRow = shtData.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Set rngAllData = shtData.Range("A1:K" & lngLastRow)

'Based on the example provided by Microsoft here
'https://msdn.microsoft.com/en-us/library/office/ff839746.aspx

With rngAllData
    Set rngCell = .Find(What:="Investor Ref :", LookIn:=xlValues)
    If Not rngCell Is Nothing Then
        strFirstAddress = rngCell.Address
        Do
            rngCell.Value2 = Replace(rngCell.Value2, "Investor Ref :", "")
            Set rngCell = .FindNext(rngCell)
            If rngCell Is Nothing Then Exit Sub
            If rngCell.Address = strFirstAddress Then Exit Sub
        Loop
    End If
End With

End Sub

Code should be:

Sub Replacetext()

    Dim sd As Worksheet
    Set sd = Sheets("StatementData")

    Dim sdlastrowv As Long
    sdlastrowv = sd.Cells(sd.Rows.Count, "A").End(xlUp).Row

    Dim sdalldata As Range, sdcel As Range, sdcelv As String
    Set sdalldata = sd.Range("A1", "K" & sdlastrowv)

    sd.Activate

    For Each sdcel In sdalldata
        If InStr(1, sdcel, "Investor Ref :") Then
            sdcel.Replace What:="Investor Ref :", Replacement:=""
        End If
    Next sdcel

End Sub

Changed sdcelv to sdcel and sdcel.Value.Replace ... to sdcel.Replace ....