Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help Needed...
I sent you a good answer to the same question, YESTERDAY.
-- Don Guillett SalesAid Software "sameer27p " wrote in message ... I had a question.I have a column named ItemNos..this column contains item numbers.Now,after every item number ,i need to insert three rows containing the text value 'Actual','Forecast' AND 'Variance' in the same column..this shud however go until the range of the item nos..since sometimes the report spools 15 rows while at other times it may spool 15000 rows... Item Number A F V Item Number A F V |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help Needed...
Time this vs my suggestion
-- Don Guillett SalesAid Software "Rollin_Again " wrote in message ... 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 Rollin --- Message posted from http://www.ExcelForum.com/ |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting Rows With Non-Needed Data between Needed Data | Excel Worksheet Functions | |||
help needed | Excel Worksheet Functions | |||
Help needed | New Users to Excel | |||
Help needed | Excel Worksheet Functions | |||
help needed | Excel Worksheet Functions |