Sub social()

Application.CommandBars("Task Pane").Visible = False

ChDir "C:\"

Workbooks.OpenText Filename:="C:\temp.txt", Origin:=437, StartRow:=1, _

DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(11, 1)), _

TrailingMinusNumbers:=True

Cells.Replace What:="empty", Replacement:="0", LookAt:=xlPart, _

SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

ReplaceFormat:=False

x = 21

y = 21

x1 = 6

y1 = 1

a = 1

line01:

Cells(y1, 8).Value = Mid(Cells(x1, 2), 1, 3) & Mid(Cells(x1, 2), 5, 2) & Mid(Cells(x1, 2), 8, 4)

Cells(y1, 9).Value = Mid(Cells(x1 + 1, 2), 4, 40)

Cells(y1, 10).Value = Mid(Cells(x1 + 2, 2), 4, 40)

Cells(y1, 11).Value = Mid(Cells(x1 + 3, 2), 4, 40)

'Cells(y1, 12).Value = Cells(x1 + 11, 1)

'Cells(y1, 13).Value = Mid(Cells(x1 + 11, 2), 1, 2)

'Cells(y1, 14).Value = Mid(Cells(x1 + 11, 2), 4, 5)

Cells(y1, 16).Value = Cells(x1 + 4, 2)

Cells(y1, 17).Value = Cells(x1 + 5, 2)

Cells(y1, 18).Value = Cells(x1 + 6, 2)

Cells(y1, 19).Value = Cells(x1 + 14, 2)

Cells(y1, 20).Value = -Cells(x1 + 8, 2)

Cells(y1, 21).Value = -Cells(x1 + 9, 2)

Cells(y1, 22).Value = -Cells(x1 + 10, 2)

Cells(y1, 23).Value = -Cells(x1 + 15, 2)

Cells(y1, 24).Value = -(Cells(y1, 17) - Cells(y1, 16))

If x1 = 552 Then Exit Sub

If a = 1 Then GoTo line02

If a = 2 Then GoTo line03

line02:

x1 = x1 + x

y1 = y1 + 1

a = 2

'If Cells(x1 - 4, 4) > 0 Then x1 = x1 - 1

GoTo line01

line03:

x1 = x1 + y

y1 = y1 + 1

a = 1

'If Cells(x1 - 4, 4) > 0 Then x1 = x1 - 1

GoTo line01

End Sub

The problem is that when I open the file temp.txt, it will only create a column a and b for my temp.txt file. It won't replace EMPTY with 0 or run my code starting with x=21. I wind up with 2 Excel windows. One called Book1 and the other one temp.txt. There is nothing in the book1 window, and temp.txt window has my temp.txt file in it with 2 columns. What do I need to get Excel to replace EMPTY with a 0 and run the code starting with X=21 on my temp.txt file? I'd appreciate any help. Thanks.

PS

The temp file has data in it that looks like this:

page: 3

BoxB: 11-1111111

BoxC-L1: company x

BoxC-L2: 100 sample st

BoxC-L3: sample city, WI 55555

BoxD: 111-11-1111

BoxE-L1: Bob Sample

BoxE-L2: 933 sample st

BoxE-L3: any city, wi 55555

box1: $1

box3: $2

box5: $3

box7: EMPTY

box2: $1

box4: $2

box6: $3

box8: EMPTY

box15a: WI

box15b: 999999

box16: $1

box17: $2