XL2K

The layout of data is in the following format, with the number of
entries against each item in Col A dynamic. Data will always commence
at A12. Col A entries will always be 3 alpha and Col B will always be
in date format. Each series of entries against an entry in Col A will
end with blank cells in Cols A & B, followed by a line of text in Col
B, followed by 2 blanks cells in A & B.

Taking the example, I wish to copy A12 down Col A (subject to there
being a corresponding entry in Col B), then stop when a blank cell in
Col B is found (eg B16). Then go to each entry in Col B (eg CLC, CLF
etc. etc) and invoke the same copy/paste criteria until all entries in
Col B have been accounted for.

Naturally if there is only 1 entry against data in Col A (eg Row 20),
no action is required.


A B
12 CLB 23/4/2003
13 30/4/2003
14 30/4/2003
15 2/5/2003
16
17 Sub Total Text
18
19
20 CLC 22/4/2003
25
26 SubTotal Text
27
28
29 CLF 28/4/2003
30 28/4/2003
31 29/4/2003
32
33 SubTotal Text
34
35
36 CLG
37
38


Any assistance with code to achieve the task would be greatly
appreciated.

Cheers

Bob
Maitland Australia

Sorry,

Some typos in the 2nd paragraph.

It should read:

Taking the example, I wish to copy A12 down Col A (subject to there
being a corresponding entry in Col B), then stop when a blank cell in
Col B is found (eg B16). Then go to each entry in Col A (eg CLC, CLF
etc. etc) and invoke the same copy/paste criteria until all entries in
Col A have been accounted for.


Cheers

Bob
Maitland Australia


On Fri, 08 Aug 2003 17:11:08 +1000, Ozbobeee
wrote:

XL2K

The layout of data is in the following format, with the number of
entries against each item in Col A dynamic. Data will always commence
at A12. Col A entries will always be 3 alpha and Col B will always be
in date format. Each series of entries against an entry in Col A will
end with blank cells in Cols A & B, followed by a line of text in Col
B, followed by 2 blanks cells in A & B.

Taking the example, I wish to copy A12 down Col A (subject to there
being a corresponding entry in Col B), then stop when a blank cell in
Col B is found (eg B16). Then go to each entry in Col B (eg CLC, CLF
etc. etc) and invoke the same copy/paste criteria until all entries in
Col B have been accounted for.

Naturally if there is only 1 entry against data in Col A (eg Row 20),
no action is required.


A B
12 CLB 23/4/2003
13 30/4/2003
14 30/4/2003
15 2/5/2003
16
17 Sub Total Text
18
19
20 CLC 22/4/2003
25
26 SubTotal Text
27
28
29 CLF 28/4/2003
30 28/4/2003
31 29/4/2003
32
33 SubTotal Text
34
35
36 CLG
37
38


Any assistance with code to achieve the task would be greatly
appreciated.

Cheers

Bob
Maitland Australia

If this were a one-time-shot, I don't think I'd use a macro.

Select your range in column A (A12:A38)
Then Edit|goto|Special|click Blanks
then type = (equal sign)
hit up arrow key
hit ctrl-enter.

The blank cells are filled with a formula that point at the cell above. Now
convert that range to values (edit|copy, edit|paste special|values).

Apply Data|Filter|autofilter to column B.
Filter on blanks.
select the cells in column A (within your range (a12:A38))
hit the delete key.

Now filter using "contains" "subtotal" or whatever is unique in that cell.
select the cells in column A again
and hit the delete key.

Remove the filter.

Debra Dalgleish has nicer instructions for this kind of thing at:
http://www.contextures.com/xlDataEntry02.html

But as a macro, I'd just loop through the rows:

Option Explicit
Sub testme01()

Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
FirstRow = 12
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

For iRow = FirstRow + 1 To LastRow 'first row is already ok!
If IsEmpty(.Cells(iRow, "A")) Then
If IsDate(.Cells(iRow, "B").Value) Then
.Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value
End If
End If
Next iRow
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


Ozbobeee wrote:

Sorry,

Some typos in the 2nd paragraph.

It should read:

Taking the example, I wish to copy A12 down Col A (subject to there
being a corresponding entry in Col B), then stop when a blank cell in
Col B is found (eg B16). Then go to each entry in Col A (eg CLC, CLF
etc. etc) and invoke the same copy/paste criteria until all entries in
Col A have been accounted for.

Cheers

Bob
Maitland Australia

On Fri, 08 Aug 2003 17:11:08 +1000, Ozbobeee
wrote:

XL2K

The layout of data is in the following format, with the number of
entries against each item in Col A dynamic. Data will always commence
at A12. Col A entries will always be 3 alpha and Col B will always be
in date format. Each series of entries against an entry in Col A will
end with blank cells in Cols A & B, followed by a line of text in Col
B, followed by 2 blanks cells in A & B.

Taking the example, I wish to copy A12 down Col A (subject to there
being a corresponding entry in Col B), then stop when a blank cell in
Col B is found (eg B16). Then go to each entry in Col B (eg CLC, CLF
etc. etc) and invoke the same copy/paste criteria until all entries in
Col B have been accounted for.

Naturally if there is only 1 entry against data in Col A (eg Row 20),
no action is required.


A B
12 CLB 23/4/2003
13 30/4/2003
14 30/4/2003
15 2/5/2003
16
17 Sub Total Text
18
19
20 CLC 22/4/2003
25
26 SubTotal Text
27
28
29 CLF 28/4/2003
30 28/4/2003
31 29/4/2003
32
33 SubTotal Text
34
35
36 CLG
37
38


Any assistance with code to achieve the task would be greatly
appreciated.

Cheers

Bob
Maitland Australia

--

Dave Peterson

Thanks Dave,

No this is not a one-off, but will be run on numerous occasions.

The sample I provided may have given the wrong impression. In reality,
the number of entries to check will vary and could go into the
thousands, so your macro is just what the doctor ordered.

Again, thanks for your reply.

Cheers

Bob
Maitland Australia



On Fri, 08 Aug 2003 21:03:02 -0500, Dave Peterson
wrote:

If this were a one-time-shot, I don't think I'd use a macro.

Select your range in column A (A12:A38)
Then Edit|goto|Special|click Blanks
then type = (equal sign)
hit up arrow key
hit ctrl-enter.

The blank cells are filled with a formula that point at the cell above. Now
convert that range to values (edit|copy, edit|paste special|values).

Apply Data|Filter|autofilter to column B.
Filter on blanks.
select the cells in column A (within your range (a12:A38))
hit the delete key.

Now filter using "contains" "subtotal" or whatever is unique in that cell.
select the cells in column A again
and hit the delete key.

Remove the filter.

Debra Dalgleish has nicer instructions for this kind of thing at:
http://www.contextures.com/xlDataEntry02.html

But as a macro, I'd just loop through the rows:

Option Explicit
Sub testme01()

Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
FirstRow = 12
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

For iRow = FirstRow + 1 To LastRow 'first row is already ok!
If IsEmpty(.Cells(iRow, "A")) Then
If IsDate(.Cells(iRow, "B").Value) Then
.Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value
End If
End If
Next iRow
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


Ozbobeee wrote:

Sorry,

Some typos in the 2nd paragraph.

It should read:

Taking the example, I wish to copy A12 down Col A (subject to there
being a corresponding entry in Col B), then stop when a blank cell in
Col B is found (eg B16). Then go to each entry in Col A (eg CLC, CLF
etc. etc) and invoke the same copy/paste criteria until all entries in
Col A have been accounted for.

Cheers

Bob
Maitland Australia

On Fri, 08 Aug 2003 17:11:08 +1000, Ozbobeee
wrote:

XL2K

The layout of data is in the following format, with the number of
entries against each item in Col A dynamic. Data will always commence
at A12. Col A entries will always be 3 alpha and Col B will always be
in date format. Each series of entries against an entry in Col A will
end with blank cells in Cols A & B, followed by a line of text in Col
B, followed by 2 blanks cells in A & B.

Taking the example, I wish to copy A12 down Col A (subject to there
being a corresponding entry in Col B), then stop when a blank cell in
Col B is found (eg B16). Then go to each entry in Col B (eg CLC, CLF
etc. etc) and invoke the same copy/paste criteria until all entries in
Col B have been accounted for.

Naturally if there is only 1 entry against data in Col A (eg Row 20),
no action is required.


A B
12 CLB 23/4/2003
13 30/4/2003
14 30/4/2003
15 2/5/2003
16
17 Sub Total Text
18
19
20 CLC 22/4/2003
25
26 SubTotal Text
27
28
29 CLF 28/4/2003
30 28/4/2003
31 29/4/2003
32
33 SubTotal Text
34
35
36 CLG
37
38


Any assistance with code to achieve the task would be greatly
appreciated.

Cheers

Bob
Maitland Australia