I am new to writing a Macro. Here is what I am looking to do:
I have a file that is approx 4000 lines long. Every 8 cells in a column I
have a new subtotal. And in between the 8 cells I have a cell that says 2
printed. What I would like to do is move the cell that says 2 printed up
next to the subtotal. Is it possible to say after every four cells in column
A move to column.

HEre is an example of what I am looking to do:
A B
1 SUBTTL
2
3
4 2 PRINTED
5
6
7
8 SUBTTL
9
10
11
12 5 PRINTED

Move 2 printed to cell B1 and move 5 printed in cell a 12 to B8 and so on.

Thanks.

Sub Test()
Dim iLastRow As Long
Dim i As Long
Dim rng As Range
Dim sTmp

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Cells(i, "A").Value Like "*PRINTED*" Then
sTmp = Cells(i, "A").Value
If rng Is Nothing Then
Set rng = Rows(i)
Else
Set rng = Union(rng, Rows(i))
End If
ElseIf Cells(i, "A").Value Like "SUBTTL" Then
Cells(i, "B").Value = sTmp
sTmp = ""
End If
Next i

If Not rng Is Nothing Then rng.Delete

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"nazzoli" wrote in message
...
I am new to writing a Macro. Here is what I am looking to do:
I have a file that is approx 4000 lines long. Every 8 cells in a column I
have a new subtotal. And in between the 8 cells I have a cell that says 2
printed. What I would like to do is move the cell that says 2 printed up
next to the subtotal. Is it possible to say after every four cells in
column
A move to column.

HEre is an example of what I am looking to do:
A B
1 SUBTTL
2
3
4 2 PRINTED
5
6
7
8 SUBTTL
9
10
11
12 5 PRINTED

Move 2 printed to cell B1 and move 5 printed in cell a 12 to B8 and so on.

Thanks.

I input all of the information and I receive a Compile Error: Sub or Function
not defined error

"Bob Phillips" wrote:

Sub Test()
Dim iLastRow As Long
Dim i As Long
Dim rng As Range
Dim sTmp

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Cells(i, "A").Value Like "*PRINTED*" Then
sTmp = Cells(i, "A").Value
If rng Is Nothing Then
Set rng = Rows(i)
Else
Set rng = Union(rng, Rows(i))
End If
ElseIf Cells(i, "A").Value Like "SUBTTL" Then
Cells(i, "B").Value = sTmp
sTmp = ""
End If
Next i

If Not rng Is Nothing Then rng.Delete

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"nazzoli" wrote in message
...
I am new to writing a Macro. Here is what I am looking to do:
I have a file that is approx 4000 lines long. Every 8 cells in a column I
have a new subtotal. And in between the 8 cells I have a cell that says 2
printed. What I would like to do is move the cell that says 2 printed up
next to the subtotal. Is it possible to say after every four cells in
column
A move to column.

HEre is an example of what I am looking to do:
A B
1 SUBTTL
2
3
4 2 PRINTED
5
6
7
8 SUBTTL
9
10
11
12 5 PRINTED

Move 2 printed to cell B1 and move 5 printed in cell a 12 to B8 and so on.

Thanks.

Where?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"nazzoli" wrote in message
...
I input all of the information and I receive a Compile Error: Sub or
Function
not defined error

"Bob Phillips" wrote:

Sub Test()
Dim iLastRow As Long
Dim i As Long
Dim rng As Range
Dim sTmp

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Cells(i, "A").Value Like "*PRINTED*" Then
sTmp = Cells(i, "A").Value
If rng Is Nothing Then
Set rng = Rows(i)
Else
Set rng = Union(rng, Rows(i))
End If
ElseIf Cells(i, "A").Value Like "SUBTTL" Then
Cells(i, "B").Value = sTmp
sTmp = ""
End If
Next i

If Not rng Is Nothing Then rng.Delete

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"nazzoli" wrote in message
...
I am new to writing a Macro. Here is what I am looking to do:
I have a file that is approx 4000 lines long. Every 8 cells in a
column I
have a new subtotal. And in between the 8 cells I have a cell that
says 2
printed. What I would like to do is move the cell that says 2 printed
up
next to the subtotal. Is it possible to say after every four cells in
column
A move to column.

HEre is an example of what I am looking to do:
A B
1 SUBTTL
2
3
4 2 PRINTED
5
6
7
8 SUBTTL
9
10
11
12 5 PRINTED

Move 2 printed to cell B1 and move 5 printed in cell a 12 to B8 and so
on.

Thanks.

The yellow box pulls up at the Sub Test() portion.

"Bob Phillips" wrote:

Where?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"nazzoli" wrote in message
...
I input all of the information and I receive a Compile Error: Sub or
Function
not defined error

"Bob Phillips" wrote:

Sub Test()
Dim iLastRow As Long
Dim i As Long
Dim rng As Range
Dim sTmp

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Cells(i, "A").Value Like "*PRINTED*" Then
sTmp = Cells(i, "A").Value
If rng Is Nothing Then
Set rng = Rows(i)
Else
Set rng = Union(rng, Rows(i))
End If
ElseIf Cells(i, "A").Value Like "SUBTTL" Then
Cells(i, "B").Value = sTmp
sTmp = ""
End If
Next i

If Not rng Is Nothing Then rng.Delete

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"nazzoli" wrote in message
...
I am new to writing a Macro. Here is what I am looking to do:
I have a file that is approx 4000 lines long. Every 8 cells in a
column I
have a new subtotal. And in between the 8 cells I have a cell that
says 2
printed. What I would like to do is move the cell that says 2 printed
up
next to the subtotal. Is it possible to say after every four cells in
column
A move to column.

HEre is an example of what I am looking to do:
A B
1 SUBTTL
2
3
4 2 PRINTED
5
6
7
8 SUBTTL
9
10
11
12 5 PRINTED

Move 2 printed to cell B1 and move 5 printed in cell a 12 to B8 and so
on.

Thanks.

Yellow box? What do you mean?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"nazzoli" wrote in message
...
The yellow box pulls up at the Sub Test() portion.

"Bob Phillips" wrote:

Where?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"nazzoli" wrote in message
...
I input all of the information and I receive a Compile Error: Sub or
Function
not defined error

"Bob Phillips" wrote:

Sub Test()
Dim iLastRow As Long
Dim i As Long
Dim rng As Range
Dim sTmp

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Cells(i, "A").Value Like "*PRINTED*" Then
sTmp = Cells(i, "A").Value
If rng Is Nothing Then
Set rng = Rows(i)
Else
Set rng = Union(rng, Rows(i))
End If
ElseIf Cells(i, "A").Value Like "SUBTTL" Then
Cells(i, "B").Value = sTmp
sTmp = ""
End If
Next i

If Not rng Is Nothing Then rng.Delete

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"nazzoli" wrote in message
...
I am new to writing a Macro. Here is what I am looking to do:
I have a file that is approx 4000 lines long. Every 8 cells in a
column I
have a new subtotal. And in between the 8 cells I have a cell
that
says 2
printed. What I would like to do is move the cell that says 2
printed
up
next to the subtotal. Is it possible to say after every four
cells in
column
A move to column.

HEre is an example of what I am looking to do:
A B
1 SUBTTL
2
3
4 2 PRINTED
5
6
7
8 SUBTTL
9
10
11
12 5 PRINTED

Move 2 printed to cell B1 and move 5 printed in cell a 12 to B8
and so
on.

Thanks.

Sorry, a yellow highlight with a yellow arrow pops up next to Sub Test()

"Bob Phillips" wrote:

Yellow box? What do you mean?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"nazzoli" wrote in message
...
The yellow box pulls up at the Sub Test() portion.

"Bob Phillips" wrote:

Where?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"nazzoli" wrote in message
...
I input all of the information and I receive a Compile Error: Sub or
Function
not defined error

"Bob Phillips" wrote:

Sub Test()
Dim iLastRow As Long
Dim i As Long
Dim rng As Range
Dim sTmp

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Cells(i, "A").Value Like "*PRINTED*" Then
sTmp = Cells(i, "A").Value
If rng Is Nothing Then
Set rng = Rows(i)
Else
Set rng = Union(rng, Rows(i))
End If
ElseIf Cells(i, "A").Value Like "SUBTTL" Then
Cells(i, "B").Value = sTmp
sTmp = ""
End If
Next i

If Not rng Is Nothing Then rng.Delete

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"nazzoli" wrote in message
...
I am new to writing a Macro. Here is what I am looking to do:
I have a file that is approx 4000 lines long. Every 8 cells in a
column I
have a new subtotal. And in between the 8 cells I have a cell
that
says 2
printed. What I would like to do is move the cell that says 2
printed
up
next to the subtotal. Is it possible to say after every four
cells in
column
A move to column.

HEre is an example of what I am looking to do:
A B
1 SUBTTL
2
3
4 2 PRINTED
5
6
7
8 SUBTTL
9
10
11
12 5 PRINTED

Move 2 printed to cell B1 and move 5 printed in cell a 12 to B8
and so
on.

Thanks.

I have no idea what that is, are you stepping through the code?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"nazzoli" wrote in message
...
Sorry, a yellow highlight with a yellow arrow pops up next to Sub Test()

"Bob Phillips" wrote:

Yellow box? What do you mean?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"nazzoli" wrote in message
...
The yellow box pulls up at the Sub Test() portion.

"Bob Phillips" wrote:

Where?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"nazzoli" wrote in message
...
I input all of the information and I receive a Compile Error: Sub
or
Function
not defined error

"Bob Phillips" wrote:

Sub Test()
Dim iLastRow As Long
Dim i As Long
Dim rng As Range
Dim sTmp

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Cells(i, "A").Value Like "*PRINTED*" Then
sTmp = Cells(i, "A").Value
If rng Is Nothing Then
Set rng = Rows(i)
Else
Set rng = Union(rng, Rows(i))
End If
ElseIf Cells(i, "A").Value Like "SUBTTL" Then
Cells(i, "B").Value = sTmp
sTmp = ""
End If
Next i

If Not rng Is Nothing Then rng.Delete

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing
direct)

"nazzoli" wrote in message
...
I am new to writing a Macro. Here is what I am looking to do:
I have a file that is approx 4000 lines long. Every 8 cells
in a
column I
have a new subtotal. And in between the 8 cells I have a cell
that
says 2
printed. What I would like to do is move the cell that says 2
printed
up
next to the subtotal. Is it possible to say after every four
cells in
column
A move to column.

HEre is an example of what I am looking to do:
A B
1 SUBTTL
2
3
4 2 PRINTED
5
6
7
8 SUBTTL
9
10
11
12 5 PRINTED

Move 2 printed to cell B1 and move 5 printed in cell a 12 to
B8
and so
on.

Thanks.

I am stepping thru the code,but it gets stuck on the first line and won't let
me go past it.
The error pops up. I hit ok and then a yellow highlight, highlights Sub
Test().

"Bob Phillips" wrote:

I have no idea what that is, are you stepping through the code?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"nazzoli" wrote in message
...
Sorry, a yellow highlight with a yellow arrow pops up next to Sub Test()

"Bob Phillips" wrote:

Yellow box? What do you mean?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"nazzoli" wrote in message
...
The yellow box pulls up at the Sub Test() portion.

"Bob Phillips" wrote:

Where?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"nazzoli" wrote in message
...
I input all of the information and I receive a Compile Error: Sub
or
Function
not defined error

"Bob Phillips" wrote:

Sub Test()
Dim iLastRow As Long
Dim i As Long
Dim rng As Range
Dim sTmp

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Cells(i, "A").Value Like "*PRINTED*" Then
sTmp = Cells(i, "A").Value
If rng Is Nothing Then
Set rng = Rows(i)
Else
Set rng = Union(rng, Rows(i))
End If
ElseIf Cells(i, "A").Value Like "SUBTTL" Then
Cells(i, "B").Value = sTmp
sTmp = ""
End If
Next i

If Not rng Is Nothing Then rng.Delete

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing
direct)

"nazzoli" wrote in message
...
I am new to writing a Macro. Here is what I am looking to do:
I have a file that is approx 4000 lines long. Every 8 cells
in a
column I
have a new subtotal. And in between the 8 cells I have a cell
that
says 2
printed. What I would like to do is move the cell that says 2
printed
up
next to the subtotal. Is it possible to say after every four
cells in
column
A move to column.

HEre is an example of what I am looking to do:
A B
1 SUBTTL
2
3
4 2 PRINTED
5
6
7
8 SUBTTL
9
10
11
12 5 PRINTED

Move 2 printed to cell B1 and move 5 printed in cell a 12 to
B8
and so
on.

Thanks.

What, on Sub Test()?

Can you post the workbook somewhere, cjoint.com does a temporary hosting
service.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"nazzoli" wrote in message
...
I am stepping thru the code,but it gets stuck on the first line and won't
let
me go past it.
The error pops up. I hit ok and then a yellow highlight, highlights Sub
Test().

"Bob Phillips" wrote:

I have no idea what that is, are you stepping through the code?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"nazzoli" wrote in message
...
Sorry, a yellow highlight with a yellow arrow pops up next to Sub
Test()

"Bob Phillips" wrote:

Yellow box? What do you mean?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"nazzoli" wrote in message
...
The yellow box pulls up at the Sub Test() portion.

"Bob Phillips" wrote:

Where?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing
direct)

"nazzoli" wrote in message
...
I input all of the information and I receive a Compile Error:
Sub
or
Function
not defined error

"Bob Phillips" wrote:

Sub Test()
Dim iLastRow As Long
Dim i As Long
Dim rng As Range
Dim sTmp

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Cells(i, "A").Value Like "*PRINTED*" Then
sTmp = Cells(i, "A").Value
If rng Is Nothing Then
Set rng = Rows(i)
Else
Set rng = Union(rng, Rows(i))
End If
ElseIf Cells(i, "A").Value Like "SUBTTL" Then
Cells(i, "B").Value = sTmp
sTmp = ""
End If
Next i

If Not rng Is Nothing Then rng.Delete

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing
direct)

"nazzoli" wrote in
message
...
I am new to writing a Macro. Here is what I am looking to
do:
I have a file that is approx 4000 lines long. Every 8
cells
in a
column I
have a new subtotal. And in between the 8 cells I have a
cell
that
says 2
printed. What I would like to do is move the cell that
says 2
printed
up
next to the subtotal. Is it possible to say after every
four
cells in
column
A move to column.

HEre is an example of what I am looking to do:
A B
1 SUBTTL
2
3
4 2 PRINTED
5
6
7
8 SUBTTL
9
10
11
12 5 PRINTED

Move 2 printed to cell B1 and move 5 printed in cell a 12
to
B8
and so
on.

Thanks.

This should do the trick


Code
-------------------
Sub MovePrinted()
Dim i As Integer
Dim c As Range

For Each c In Range("A:A")
If UCase(Right(c.Value, 3)) = "TTL" Then
i = 0
End If
If UCase(Right(c.Value, 7)) = "PRINTED" Then
Range(c.Address).Offset(i, 1).Value = c.Value
c.Value = ""
Else
i = i - 1
End If
Next
End Su
-------------------

--
Excelenato

-----------------------------------------------------------------------
Excelenator's Profile: http://www.excelforum.com/member.php...fo&userid=3676
View this thread: http://www.excelforum.com/showthread.php?threadid=56513

Ooops forgot to limit the loop. If "i" ever gets smaller than -10 th
For Loop will be exited and the macro will end. This way after th
4000th row the macro will not run until the 65,536th row looking fo
TTL or PRINTED in the cell values. I used -10 since you said that th
subtotal occurs every 8 rows. It does not matter how many rows ar
between the subtotal and the printed comment.


Code
-------------------
Sub MovePrinted()
Dim i As Integer
Dim c As Range

For Each c In Range("A:A")
If i < -10 Then
Exit For
End If
If UCase(Right(c.Value, 3)) = "TTL" Then
i = 0
End If
If UCase(Right(c.Value, 7)) = "PRINTED" Then
Range(c.Address).Offset(i, 1).Value = c.Value
c.Value = ""
Else
i = i - 1
End If
Next
End Su
-------------------

--
Excelenato

-----------------------------------------------------------------------
Excelenator's Profile: http://www.excelforum.com/member.php...fo&userid=3676
View this thread: http://www.excelforum.com/showthread.php?threadid=56513

This worked. Thanks.

"Excelenator" wrote:


This should do the trick


Code:
--------------------
Sub MovePrinted()
Dim i As Integer
Dim c As Range

For Each c In Range("A:A")
If UCase(Right(c.Value, 3)) = "TTL" Then
i = 0
End If
If UCase(Right(c.Value, 7)) = "PRINTED" Then
Range(c.Address).Offset(i, 1).Value = c.Value
c.Value = ""
Else
i = i - 1
End If
Next
End Sub
--------------------


--
Excelenator


------------------------------------------------------------------------
Excelenator's Profile: http://www.excelforum.com/member.php...o&userid=36768
View this thread: http://www.excelforum.com/showthread...hreadid=565130