VBA 1004 Application defined or Object defned error

Question

While running this code, I am getting

VBA 1004 error: "Application defined or Object defined error"

on the line indicated below. Why?

I am using Excel 2013. My spreadsheet has 2 sheets, Sheet1 and Sheet2.

Public Sub ImportTextFile()

Dim RowNdx As Long
Dim TempVal As String
Dim WholeLine As String

Application.ScreenUpdating = False
'On Error GoTo EndMacro:
RowNdx = 11
FName = Worksheets("Sheet1").Cells(1, 2).Value

Open FName For Input Access Read As #1

While Not EOF(1)
    Line Input #1, WholeLine
    Worksheets("Sheet1").Cells(RowNdx, 1).Value = WholeLine ' <~~~~~~ ERROR
    RowNdx = RowNdx + 1
Wend

Application.ScreenUpdating = True
Close #1

End Sub

Not enough info to reproduce problem. Try stepping through your code in debug mode and watching how your variables evolve (instructions). What's the value of RowNdx and WholeLine when the error happens? — Jean-François Corbett
RowNdx = 11 when the error occurs. I am reading from a simple text file (containing 450 rows). So Wholeline at the time of error contains "ABCxyz". Again this code is a simple code to read from a text file and copy its whole contents to the excel spreadsheet from Row 11 onwards. I also tried to step thru the code. In the While loop, I tried to comment the Worksheets("Sheet1").Cells(RowNdx, 1).Value = WholeLine during the first run through the loop. During the second iteration of the loop, I uncommented this line. It then ran through fine till the end. — Manish
I wonder if there's something in WholeLine that Excel doesn't like. Something like a Chr(13) without Chr(10) although that's not it. Can you loop through each letter of WholeLine on the line that causes the error and Debug.Print Mid$(WholeLine,i,1), Asc(Mid$(WholeLine,i,1)) where i is your control variable? It might be useful to see what kind of characters are in there. — Dick Kusleika
Yes. @DickKusLeika, you are right. My file had the following line. It was causing me the error. =~=~=~=~=~=~=~=~=~=~=~= PuTTY log 2015.04.10 14:12:25 =~=~=~=~=~=~=~=~=~=~=~= I removed that line from the text file and the code worked fine. Seems like Excel didnt like the '=' as the first character in the text file. Is there a way to copy all the lines from the text file, no matter what it contains? Many thanks for pointing me in the right direction. I had spend quite a few hours changing my code without success. — Manish

Dick Kusleika Dick Kusleika · Accepted Answer · 2015-04-13T14:46:41

If the first character of your line is an equal sign, Excel will think you're entering a formula. And the line in your comment that starts with an equal sign is not a valid formula - no surprise there! Excel complains because you're trying to enter a formula that's not valid.

You can prefix an apostrophe

Worksheets("Sheet1").Cells(RowNdx, 1).Value = "'" & WholeLine

That will treat every line like text - it won't convert date strings to dates are numerics to actual numbers and leaves everything just as it is. It's probably a better option that checking for an equal sign explicitly because then you also have to check for a plus sign, minus sign, etc. So keep everything as a string with the leading apostrophe unless you have a good reason not to.

VBA 1004 Application defined or Object defned error

1 Answers