N
Nick R
Hi all
I'm a bit rusty at VB - so forgive basic errors please. I've searched and
searched online help and google groups, but to no avail. I think my
programming knowledge may be a bit basic for what I'm trying to do.
Full details below - the question I have is this: how do I get the cells to
copy down, and then to get the cut'n'paste to work so I can parse the data
again? Driving me mad and I've spent nearly all day today on it...
(
Kind regards
Nick
(email address in headers, as cunningly disguised as I can muster at this
time of day)
ps - any help on "streamlining" my appalling code very much welcomed.
--------------------------------------------------------------------------
Our accounts system has been set to only output data as fixed width text
files (can't get it changed - not for discussion). Example is below:
621660 A COMPANY LTD
LONDON 06937829 22,289.48 Yes
10 140270603 30-JUL-03 ROY0603
231.78 0.00 231.78
7920 AP
Invoice 158307 30-JUL-03
20 128690603 05-AUG-03 ROY0603
161.18 0.00 161.18
7920 AP
Invoice 158971 05-AUG-03
30 130730603 27-AUG-03 ROY0603
102.98 0.00 102.98
7920 AP
Invoice 161509 27-AUG-03
40 150200603 10-SEP-03 ROY0603
164.64 0.00 164.64
7920 AP
Invoice 163881 10-SEP-03
50 151590603 10-SEP-03 ROY0603
171.38 0.00 171.38
7920 AP
Invoice 163882 10-SEP-03
60 144670603 17-SEP-03 ROY0603
21,457.52 0.00 21,457.52
7920 AP
Invoice 165187 17-SEP-03
I've tried to write a macro to take each line and move it to the top of the
sheet (or next available line) and parse it. I've done it in 3 steps to
reflect the three different lines of data that's there.
Step 1:
-----------------
Sub AP_Line1()
'
' AP_Line1 Macro
' Macro recorded 11/02/2004 by Nick Read
'
' Move data to topmost row
Selection.Cut
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
' Convert to columns
Selection.TextToColumns Destination:=ActiveCell, DataType:=xlFixedWidth,
_
FieldInfo:=Array(Array(0, 9), Array(9, 2), Array(15, 9), Array(50,
2), Array(74, 2), _
Array(91, 2), Array(99, 1), Array(124, 9))
' Move the cursor to the next row, first cell ready for part 2
Selection.End(xlToLeft).Select
Selection.End(xlDown).Select
End Sub
--------------------
works fine.
Step 2:
----------------------
Sub AP_line2()
'
' AP_line2 Macro
' Macro recorded 11/02/2004 by Nick Read
'
'
Selection.Cut
Selection.End(xlUp).Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
Selection.TextToColumns Destination:=ActiveCell, DataType:=xlFixedWidth,
_
FieldInfo:=Array(Array(0, 9), Array(25, 2), Array(34, 9), Array(39,
4), Array(48, 2), _
Array(60, 1), Array(86, 1), Array(105, 1), Array(124, 9))
' Move the cursor to the next row, first cell ready for part 3
Selection.End(xlToLeft).Select
Selection.End(xlDown).Select
End Sub
--------------------
Also works fine.
Step 3 not really necessary to show here is to do the same with the third
line.
Problem comes when I try to copy the first line to the row beneath it, and
then go back to the next row of unparsed data and move and parse it.
viz. step 2a
------------------
Sub AP_line2a()
'
' AP_line2a Macro
' Macro recorded 11/02/2004 by Nick Read
'
'
Selection.End(xlUp).Select
Selection.Resize(Selection.Rows.Count + 0, Selection.Columns.Count +
5).Select
Selection.Copy
Selection.End(xlToLeft).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Selection.End(xlToLeft).Select
Selection.End(xlDown).Select
' works fine to this point
' this next bit doesn't work - error is "No data to parse"
ActiveCell.Cut
Selection.End(xlUp).Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Select
Selection.TextToColumns Destination:=ActiveCell, DataType:=xlFixedWidth,
_
FieldInfo:=Array(Array(0, 9), Array(25, 2), Array(34, 9), Array(39,
4), Array(48, 2), _
Array(60, 1), Array(86, 1), Array(105, 1), Array(124, 9))
' Move the cursor to the next row, first cell ready for part 3
'Selection.End(xlToLeft).Select
'Selection.End(xlDown).Select
End Sub
--------------------------------
I'm a bit rusty at VB - so forgive basic errors please. I've searched and
searched online help and google groups, but to no avail. I think my
programming knowledge may be a bit basic for what I'm trying to do.
Full details below - the question I have is this: how do I get the cells to
copy down, and then to get the cut'n'paste to work so I can parse the data
again? Driving me mad and I've spent nearly all day today on it...
(Kind regards
Nick
(email address in headers, as cunningly disguised as I can muster at this
time of day)
ps - any help on "streamlining" my appalling code very much welcomed.
--------------------------------------------------------------------------
Our accounts system has been set to only output data as fixed width text
files (can't get it changed - not for discussion). Example is below:
621660 A COMPANY LTD
LONDON 06937829 22,289.48 Yes
10 140270603 30-JUL-03 ROY0603
231.78 0.00 231.78
7920 AP
Invoice 158307 30-JUL-03
20 128690603 05-AUG-03 ROY0603
161.18 0.00 161.18
7920 AP
Invoice 158971 05-AUG-03
30 130730603 27-AUG-03 ROY0603
102.98 0.00 102.98
7920 AP
Invoice 161509 27-AUG-03
40 150200603 10-SEP-03 ROY0603
164.64 0.00 164.64
7920 AP
Invoice 163881 10-SEP-03
50 151590603 10-SEP-03 ROY0603
171.38 0.00 171.38
7920 AP
Invoice 163882 10-SEP-03
60 144670603 17-SEP-03 ROY0603
21,457.52 0.00 21,457.52
7920 AP
Invoice 165187 17-SEP-03
I've tried to write a macro to take each line and move it to the top of the
sheet (or next available line) and parse it. I've done it in 3 steps to
reflect the three different lines of data that's there.
Step 1:
-----------------
Sub AP_Line1()
'
' AP_Line1 Macro
' Macro recorded 11/02/2004 by Nick Read
'
' Move data to topmost row
Selection.Cut
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
' Convert to columns
Selection.TextToColumns Destination:=ActiveCell, DataType:=xlFixedWidth,
_
FieldInfo:=Array(Array(0, 9), Array(9, 2), Array(15, 9), Array(50,
2), Array(74, 2), _
Array(91, 2), Array(99, 1), Array(124, 9))
' Move the cursor to the next row, first cell ready for part 2
Selection.End(xlToLeft).Select
Selection.End(xlDown).Select
End Sub
--------------------
works fine.
Step 2:
----------------------
Sub AP_line2()
'
' AP_line2 Macro
' Macro recorded 11/02/2004 by Nick Read
'
'
Selection.Cut
Selection.End(xlUp).Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
Selection.TextToColumns Destination:=ActiveCell, DataType:=xlFixedWidth,
_
FieldInfo:=Array(Array(0, 9), Array(25, 2), Array(34, 9), Array(39,
4), Array(48, 2), _
Array(60, 1), Array(86, 1), Array(105, 1), Array(124, 9))
' Move the cursor to the next row, first cell ready for part 3
Selection.End(xlToLeft).Select
Selection.End(xlDown).Select
End Sub
--------------------
Also works fine.
Step 3 not really necessary to show here is to do the same with the third
line.
Problem comes when I try to copy the first line to the row beneath it, and
then go back to the next row of unparsed data and move and parse it.
viz. step 2a
------------------
Sub AP_line2a()
'
' AP_line2a Macro
' Macro recorded 11/02/2004 by Nick Read
'
'
Selection.End(xlUp).Select
Selection.Resize(Selection.Rows.Count + 0, Selection.Columns.Count +
5).Select
Selection.Copy
Selection.End(xlToLeft).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Selection.End(xlToLeft).Select
Selection.End(xlDown).Select
' works fine to this point
' this next bit doesn't work - error is "No data to parse"
ActiveCell.Cut
Selection.End(xlUp).Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Select
Selection.TextToColumns Destination:=ActiveCell, DataType:=xlFixedWidth,
_
FieldInfo:=Array(Array(0, 9), Array(25, 2), Array(34, 9), Array(39,
4), Array(48, 2), _
Array(60, 1), Array(86, 1), Array(105, 1), Array(124, 9))
' Move the cursor to the next row, first cell ready for part 3
'Selection.End(xlToLeft).Select
'Selection.End(xlDown).Select
End Sub
--------------------------------