Convertir texto a numero VBA EXCEL

Sustituye tú código por este

Sub CarlosArrocha()
Dim Counter As Integer
For Counter = 1 To 1000
Application.Worksheets("Hoja1").Cells(Counter, 14) = Val(Hoja1.Cells(Counter, 1)) 'CDbl
Next Counter
End Sub

Cambia el nombre de la hoja1 por el que tengas.

Hola Carlos no me funciona. He hecho lo que me has dicho y no me funciona, sigue apareciendo los triángulos verdes en la esquina superior izquierda

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Counter As Integer
For Counter = 1 To 1000
Application.Worksheets("Datos").Cells(Counter, 14) = Val(Hoja1.Cells(Counter, 1))
Next Counter
End Sub

Tengo activado el Option Explicit (no se si sera por eso)

Es que yo la probé en un módulo y funcionó, pero tu lo estás haciendo directamente en la hoja, así no lo probé. Colócalo en un módulo.

Si en un modulo si que me funciona.  Pero me ha dado 2 problemas (solo es pulirlo)
1. necesito que solo lo aplique en la columna N3 pero en el rango de la tabla llamado TABLA_DATOS

2. Rellena todos los datos con 0,00 (si no hay nada) (ver imagen)

Además que me sustituye los datos por 0,00

Para hacerlo más fácil hazlo así y descarta la macro anterior. Selecciona primero el rango que quieres convertir y ejecutas la macro. Para el ejemplo la he puesto en un botón, pero la puedes poner en un módulo.

Private Sub CommandButton1_Click()
'by Carlos Arrocha
Dim cel As Range
For Each cel In Selection
    cel.Value = CStr(cel)
Next cel
End Sub

Este método no me funciona, se ejecuta la macro pero no hace cambios

Ten en cuenta que se está ejecutando desde un botón

Si, la estoy activando desde un botón y también desde Visual Basic

Lo raro es que yo hago pruebas antes de enviarlo y a mí me funcionó. Te miro otra cosa un momento.

Prueba así. Debes seleccionar el rango que quieres modificar, pero si quieres que trabaje sin seleccionar cambias donde pone Selection por Range("N3:N1000")

Private Sub CommandButton1_Click()
Dim Cel As Range
For Each Cel In Selection
    If Not IsNumeric(Cel.Value) Then
        Cel.Value = WorksheetFunction.Trim(Cel.Value)
    ElseIf TypeName(Cel.Value) = "String" Then
        Cel.Value = Cel.Value + 1 - 1
    End If
Next Cel
End Sub