Macro Help...

S

sameer27p

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
 
T

Tim Coddington

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
 
D

Don Guillett

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
 
D

Don Guillett

65336 rows on later versions. Time to upgrade

--
Don Guillett
SalesAid Software
[email protected]
Tim Coddington said:
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 > said:
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
Click to expand...
Click to expand...
 
T

Tom Ogilvy

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 said:
65336 rows on later versions. Time to upgrade

--
Don Guillett
SalesAid Software
[email protected]
Tim Coddington said:
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 > said:
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
Click to expand...
Click to expand...
Click to expand...
 
Top