Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help...
I had another question.I have a column named ItemNos..this colum
contains item numbers.Now,after every item number ,i need to inser three rows containing the text value 'Actual','Forecast' AND 'Variance in the 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
|
|||
|
|||
Macro Help...
Here is a stab at it ...
Sub Macro2() [A1].Select 'This would be the first cell in your ItemNos. column Do While ActiveCell.Value < "" Range(ActiveCell.Offset(1, 0).Address & ":" & ActiveCell.Offset(3, 0).Address).EntireRow.Insert ActiveCell.Offset(1, 0) = "A" ActiveCell.Offset(2, 0) = "F" ActiveCell.Offset(3, 0) = "V" ActiveCell.Offset(4, 0).Select Loop End Sub Now if you are really getting 15000 rows, what about 16000? If you go over 16384 rows, that is more rows than allowed on an excel sheet. -Tim "sameer27p " wrote in message ... I had another 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
|
|||
|
|||
Macro Help...
He meant if you add 3 rows for each existing data row, you will have 4 x
16384 = 65536 rows -- Regards, Tom Ogilvy "Don Guillett" wrote in message ... go over 16384 rows, that is more rows than allowed on an excel sheet 65336 rows on later versions. Time to upgrade -- Don Guillett SalesAid Software "Tim Coddington" wrote in message ... Here is a stab at it ... Sub Macro2() [A1].Select 'This would be the first cell in your ItemNos. column Do While ActiveCell.Value < "" Range(ActiveCell.Offset(1, 0).Address & ":" & ActiveCell.Offset(3, 0).Address).EntireRow.Insert ActiveCell.Offset(1, 0) = "A" ActiveCell.Offset(2, 0) = "F" ActiveCell.Offset(3, 0) = "V" ActiveCell.Offset(4, 0).Select Loop End Sub Now if you are really getting 15000 rows, what about 16000? If you go over 16384 rows, that is more rows than allowed on an excel sheet. -Tim "sameer27p " wrote in message ... I had another 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help...
Try this idea. Put Actual in g11, Forecast in g12, and Variance in g13 of
another sheet and use Sub insert() x = Cells(Rows.Count, "a").End(xlUp).Row For i = x To 1 Step -1 Sheets("sheet1").Range("g11:g13").Copy Cells(i, "a").Offset(1).insert shift:=xlDown Next End Sub -- Don Guillett SalesAid Software "sameer27p " wrote in message ... I had another 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
using a cell value to control a counter inside a macro and displaying macro value | Excel Worksheet Functions | |||
macro to delete entire rows when column A is blank ...a quick macro | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |