Help Needed...

S

sameer27p

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
 
R

Rollin_Again

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
 
S

sameer27p

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
 
R

Rollin_Again

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
 
D

Don Guillett

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
 
S

sameer27p

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
 
S

sameer27p

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
 
R

Rollin_Again

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
 
Top