VB Problem

[Craig11]

I have this VB code:

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

 

[BrianB]

First off you are opening the file as Fixed Width.
I suggest you record a macro opening the file as Delimited and in the Wizard Step 2 of 3 put a colon : in the 'Other' box.

Similarly for the Edit/Replace record a macro when you get it to work manually. This can be fiddly at times.