Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Help Needed...

I had a question.I have a column named ItemNos..this column contain
item numbers.Now,after every item number ,i need to insert three row
containing the text value 'Actual','Forecast' AND 'Variance' in th
same column..this shud however go until the range of the ite
nos..since sometimes the report spools 15 rows while at other times i
may spool 15000 rows...

Item Number
A
F
V
Item Number
A
F
V
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Help Needed...

Here is a macro that will do what you want. Make sure to select th
first cell in the column containing your information before running th
macro. This macro assumes that all your data is in Column A. Jus
change the bolded letter in the code below to reflect which Colum
contains your data.


Public Sub AddItems()

Do While ActiveCell.Row <= Range("*A*65536").End(xlUp).Row

vrows = ActiveCell.Row + 1 & ":" & ActiveCell.Row + 3

Rows(vrows).Insert Shift:=xlDown

ActiveCell.Offset(1, 0).Value = "Actual"
ActiveCell.Offset(2, 0).Value = "Forecast"
ActiveCell.Offset(3, 0).Value = "Variance"
ActiveCell.Offset(4, 0).Select

Loop

End Sub







Rolli

--
Message posted from http://www.ExcelForum.com

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Help Needed...

Hey thanks Rollin_Again....

its working fine..just one more thing..what if i need to insert blan
rows after every VARIANCE row...how do i do that

--
Message posted from http://www.ExcelForum.com

  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Help Needed...

Public Sub AddItems()

Do While ActiveCell.Row <= Range("A65536").End(xlUp).Row

vrows = ActiveCell.Row + 1 & ":" & ActiveCell.Row + 4

Rows(vrows).Insert Shift:=xlDown

ActiveCell.Offset(1, 0).Value = "Actual"
ActiveCell.Offset(2, 0).Value = "Forecast"
ActiveCell.Offset(3, 0).Value = "Variance"
ActiveCell.Offset(5, 0).Select

Loop

End Sub





Rolli

--
Message posted from http://www.ExcelForum.com



  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,337
Default Help Needed...

Did you try just coping one more row g14 vs g13

Sub insert()
x = Cells(Rows.Count, "a").End(xlUp).Row
For i = x To 1 Step -1
Sheets("sheet1").Range("g11:g14").Copy
Cells(i, "a").Offset(1).insert shift:=xlDown
Next
End Sub

--
Don Guillett
SalesAid Software

"sameer27p " wrote in message
...
Hey thanks Rollin_Again....

its working fine..just one more thing..what if i need to insert blank
rows after every VARIANCE row...how do i do that ?


---
Message posted from
http://www.ExcelForum.com/



  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Help Needed...

Thanks Rollin and Don...I really appreciate your help.I need some mor
from you since I am really a novice in writing macros.I have writte
some of them and am in the learning process,but not as expert as yo
both....
I need to generate the waterfall model which is specified in the xce
sheet attached.I am done with some part of it,but I am not sure how t
plug in the exact values from the two sheets and generate th
model.........
It would be kind if you'll can have a look at it and give me som
suggestions.I need to write a macro for this.Is there some ready macr
available ?????

Waterfall1.xls is attached !

Reply awaited.....Thanks for your time and consideration..Have a nic
day ahead

Attachment filename: waterfall1.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=62149
--
Message posted from http://www.ExcelForum.com

  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Help Needed...

i need to generate this format...Roll in,the macro u suggested run
fine,but it is not able to generate the following format...i hav
attached the format in Book2.xls..Hope u cud provide some valuabl
input in acieving this format.Thanks again guys for all you
help..........................

Attachment filename: book2.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=62168
--
Message posted from http://www.ExcelForum.com

  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Help Needed...

The code is not very pretty but it works. I'm sure there is a bette
way to do this but after 4 glasses of red wine I'm not concerned abou
how the code looks ;)


Code
-------------------
*
Public Sub AddRows()

a = 1
b = 0

vrow = ActiveCell.Row

Do While ActiveCell.Row <= Range("A65536").End(xlUp).Row

Do While b < 7

Rows(vrow + 1 & ":" & vrow + 3).insert Shift:=xlDown
ActiveCell.Offset(a, b).Value = "Actual"
ActiveCell.Offset(a + 1, b).Value = "Forecast"
ActiveCell.Offset(a + 2, b).Value = "Variance"

vrow = vrow + 3
a = a + 3
b = b + 1

Loop

vrow = vrow + 2
Rows(vrow - 1).insert Shift:=xlDown
Range("A" & vrow).Select

a = 1
b = 0

Loop

End Sub
-------------------







Rolli

--
Message posted from http://www.ExcelForum.com

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
Deleting Rows With Non-Needed Data between Needed Data Daren Excel Worksheet Functions 2 September 30th 08 06:47 PM
help needed Gary Excel Worksheet Functions 1 December 6th 06 01:32 AM
Help needed Monty New Users to Excel 1 July 31st 06 09:40 AM
Help needed Gary Excel Worksheet Functions 5 July 19th 06 02:36 AM
help needed zomex Excel Worksheet Functions 5 January 4th 06 01:54 PM


All times are GMT +1. The time now is 01:46 PM.

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

About Us

"It's about Microsoft Excel"