Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old July 14th 08, 04:48 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 21
Default Inserting a row

I am sure this is easy to you experts but I have several thousands rows of
data, and I want a macro to insert two rows every time it sees a "1" in one
column. I am doing this manually at the moment and I am falling asleep. I
would also like to be able to put a sub-total in column c after the lines
have been inserted, work out what percentage each part is of the sub-total,
and highlight this by inseting a "1" in column E next to the largest
percentage. I have put a before and after below.

P.S. I can just about record a macro, so as simple as possible would be good.

Thanks in advance.

BEFORE
A B C D E F
1 1 294 A
2 2 406 total B
3 1 - F
4 2 30,728 total T
5 1 14,833 A
6 2 13,820 total X
7 1 17,763 H
8 2 16,754 A
9 3 12,564 total B


AFTER
A B C D E F
1 1 294 42% A
2 2 406 58% 1 total B
3 700
4
5 1 - 0% F
6 2 30,728 100% 1 total T
7 30,728
8
9 1 14,833 52% 1 A
10 2 13,820 48% total X
11 28,654
12
13 1 17,763 38% 1 H
14 2 16,754 36% A
15 3 12,564 27% total B
47,081


  #2   Report Post  
Old July 14th 08, 05:28 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 9,101
Default Inserting a row

Sub maketotal()

RowCount = 1
FirstRow = 1
Do While Range("A" & RowCount) < ""
If Range("B" & (RowCount + 1)) = 1 Or _
Range("B" & (RowCount + 1)) = "" Then

'add two blank row
Rows(RowCount + 1).Insert
Rows(RowCount + 1).Insert
Range("B" & (RowCount + 1)) = "Total"
Range("C" & (RowCount + 1)).Formula = _
"=Sum(C" & FirstRow & ":C" & RowCount & ")"

RowCount = RowCount + 3
FirstRow = RowCount
Else
RowCount = RowCount + 1
End If
Loop

End Sub


"Nick C" wrote:

I am sure this is easy to you experts but I have several thousands rows of
data, and I want a macro to insert two rows every time it sees a "1" in one
column. I am doing this manually at the moment and I am falling asleep. I
would also like to be able to put a sub-total in column c after the lines
have been inserted, work out what percentage each part is of the sub-total,
and highlight this by inseting a "1" in column E next to the largest
percentage. I have put a before and after below.

P.S. I can just about record a macro, so as simple as possible would be good.

Thanks in advance.

BEFORE
A B C D E F
1 1 294 A
2 2 406 total B
3 1 - F
4 2 30,728 total T
5 1 14,833 A
6 2 13,820 total X
7 1 17,763 H
8 2 16,754 A
9 3 12,564 total B


AFTER
A B C D E F
1 1 294 42% A
2 2 406 58% 1 total B
3 700
4
5 1 - 0% F
6 2 30,728 100% 1 total T
7 30,728
8
9 1 14,833 52% 1 A
10 2 13,820 48% total X
11 28,654
12
13 1 17,763 38% 1 H
14 2 16,754 36% A
15 3 12,564 27% total B
47,081

  #3   Report Post  
Old July 15th 08, 09:40 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 21
Default Inserting a row

Many thanks Joel. Could I impose on you further by asking you what to do with
it now?

Regards,

Nick

"Joel" wrote:

Sub maketotal()

RowCount = 1
FirstRow = 1
Do While Range("A" & RowCount) < ""
If Range("B" & (RowCount + 1)) = 1 Or _
Range("B" & (RowCount + 1)) = "" Then

'add two blank row
Rows(RowCount + 1).Insert
Rows(RowCount + 1).Insert
Range("B" & (RowCount + 1)) = "Total"
Range("C" & (RowCount + 1)).Formula = _
"=Sum(C" & FirstRow & ":C" & RowCount & ")"

RowCount = RowCount + 3
FirstRow = RowCount
Else
RowCount = RowCount + 1
End If
Loop

End Sub


"Nick C" wrote:

I am sure this is easy to you experts but I have several thousands rows of
data, and I want a macro to insert two rows every time it sees a "1" in one
column. I am doing this manually at the moment and I am falling asleep. I
would also like to be able to put a sub-total in column c after the lines
have been inserted, work out what percentage each part is of the sub-total,
and highlight this by inseting a "1" in column E next to the largest
percentage. I have put a before and after below.

P.S. I can just about record a macro, so as simple as possible would be good.

Thanks in advance.

BEFORE
A B C D E F
1 1 294 A
2 2 406 total B
3 1 - F
4 2 30,728 total T
5 1 14,833 A
6 2 13,820 total X
7 1 17,763 H
8 2 16,754 A
9 3 12,564 total B


AFTER
A B C D E F
1 1 294 42% A
2 2 406 58% 1 total B
3 700
4
5 1 - 0% F
6 2 30,728 100% 1 total T
7 30,728
8
9 1 14,833 52% 1 A
10 2 13,820 48% total X
11 28,654
12
13 1 17,763 38% 1 H
14 2 16,754 36% A
15 3 12,564 27% total B
47,081

  #4   Report Post  
Old July 15th 08, 01:31 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 10,124
Default Inserting a row

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Nick C" wrote in message
...
Many thanks Joel. Could I impose on you further by asking you what to do
with
it now?

Regards,

Nick

"Joel" wrote:

Sub maketotal()

RowCount = 1
FirstRow = 1
Do While Range("A" & RowCount) < ""
If Range("B" & (RowCount + 1)) = 1 Or _
Range("B" & (RowCount + 1)) = "" Then

'add two blank row
Rows(RowCount + 1).Insert
Rows(RowCount + 1).Insert
Range("B" & (RowCount + 1)) = "Total"
Range("C" & (RowCount + 1)).Formula = _
"=Sum(C" & FirstRow & ":C" & RowCount & ")"

RowCount = RowCount + 3
FirstRow = RowCount
Else
RowCount = RowCount + 1
End If
Loop

End Sub


"Nick C" wrote:

I am sure this is easy to you experts but I have several thousands rows
of
data, and I want a macro to insert two rows every time it sees a "1" in
one
column. I am doing this manually at the moment and I am falling asleep.
I
would also like to be able to put a sub-total in column c after the
lines
have been inserted, work out what percentage each part is of the
sub-total,
and highlight this by inseting a "1" in column E next to the largest
percentage. I have put a before and after below.

P.S. I can just about record a macro, so as simple as possible would be
good.

Thanks in advance.

BEFORE
A B C D E F
1 1 294 A
2 2 406 total B
3 1 - F
4 2 30,728 total T
5 1 14,833 A
6 2 13,820 total X
7 1 17,763 H
8 2 16,754 A
9 3 12,564 total B


AFTER
A B C D E F
1 1 294 42% A
2 2 406 58% 1 total B
3 700
4
5 1 - 0% F
6 2 30,728 100% 1 total T
7 30,728
8
9 1 14,833 52% 1 A
10 2 13,820 48% total X
11 28,654
12
13 1 17,763 38% 1 H
14 2 16,754 36% A
15 3 12,564 27% total B
47,081




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Inserting new row Sure Excel Worksheet Functions 2 September 20th 08 02:35 AM
inserting a row Elena Excel Programming 1 May 14th 06 05:51 PM
Inserting a row Denys[_2_] Excel Programming 3 February 24th 06 10:39 PM
inserting a row.. angelb88 Excel Discussion (Misc queries) 1 April 5th 05 05:40 PM
inserting a row bforster1[_12_] Excel Programming 1 September 12th 04 04:57 AM


All times are GMT +1. The time now is 11:34 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017