Gary and Claus, this is the problem that I presented earlier. Can anyone identify why the first function works perfectly, but the second function fails? The second function reads data from "C:\data.txt", which only contains "a123 Go now 123".

The output is supposed to be "Go now 123", but only the first function works. Both functions are essentially the same, except that the second one reads data from a file.

Is it possible that loading a file string into a variant type causes problems?

'-------------------------------------
Sub ThisWorks()

Dim sline As String

sline = "a123 Go now 123"

sline = Application.Substitute(sline, " ", "*", 1)
varTmp = Split(sline, "*")

MsgBox Trim(varTmp(1)) ' It works! It returns "Go now 123"

End Sub


'-------------------------------------
Sub ThisFails()
Dim n As Integer, i As Integer, RowNmbr As Integer
Dim varOut() As Variant
Dim sText As String
Dim sPath As String
Dim sline As String

sPath = "C:\data.txt"

'Open file for read
Open sPath For Input As #1

Do Until EOF(1)
RowNmbr = RowNmbr + 1
Line Input #1, sText
If (Len(sText) = 0) Then
GoTo NFR_ENDLOOP
End If
If StrChk(sText) = True Then
ReDim Preserve varOut(i)
varOut(i) = sText 'RowNmbr
i = i + 1
End If
NFR_ENDLOOP:
Loop
Close #1

sline = varOut(0) ' Read the first line: "a123 Go now 123"

sline = Application.Substitute(sline, " ", "*", 1)
varTmp = Split(sline, "*")

MsgBox Trim(varTmp(1)) ' Fails to output "Go now 123"

End Sub