Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with simple macro
Hello All, I do not work with excel on a daily basis, I'm actually a web developer but I have a request to modify an excel spreadsheet via a macro. Basically I have about 20 rows. One cell in each rows has a number ,i.e. A2 is 250 A3 is 400, etc. I need to write a macro to take that number in the A cell and copy the row that it exists in that many times. So if Cell A2 has a value of 250, I need to copy Row 2 250 times. I'm pretty sure I could figure this out if I scour the web and use trial and error, but I was wondering if someone could help me out. I dont think the macro would be terribly difficult. Thanks in advance, Garlnnd -- garlnnd ------------------------------------------------------------------------ garlnnd's Profile: http://www.excelforum.com/member.php...o&userid=27832 View this thread: http://www.excelforum.com/showthread...hreadid=473426 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with simple macro
Sub DupCells()
Dim cell as Range for each cell in Worksheets(1).Range("A2:A21") cell.EntireRow.Copy Destination:=worksheets(2) _ cells(rows.count,1).End(xlup)(2).Resize(cell.Value ) Next End sub Put you date in the first sheet in the tab order. It will write the copies to the second sheet in the tab order, so make sure that sheet is blank. Adjust the A2:A21 to reflect the cells on the first sheet that contain the quantities. -- Regards, Tom Ogilvy "garlnnd" wrote in message ... Hello All, I do not work with excel on a daily basis, I'm actually a web developer but I have a request to modify an excel spreadsheet via a macro. Basically I have about 20 rows. One cell in each rows has a number ,i.e. A2 is 250 A3 is 400, etc. I need to write a macro to take that number in the A cell and copy the row that it exists in that many times. So if Cell A2 has a value of 250, I need to copy Row 2 250 times. I'm pretty sure I could figure this out if I scour the web and use trial and error, but I was wondering if someone could help me out. I dont think the macro would be terribly difficult. Thanks in advance, Garlnnd -- garlnnd ------------------------------------------------------------------------ garlnnd's Profile: http://www.excelforum.com/member.php...o&userid=27832 View this thread: http://www.excelforum.com/showthread...hreadid=473426 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with simple macro
garlnnd,
Sub TryNow() Dim myRow As Long For myRow = Range("A65536").End(xlUp).Row To 1 Step -1 If IsNumeric(Cells(myRow, 1).Value) Then Cells(myRow, 1).EntireRow.Copy Cells(myRow, 1).Resize(Cells(myRow, 1).Value).EntireRow.Insert End If Next myRow End Sub This will result in you having 251 rows of the same, if the number is 250 (the original + 250 copies). If you want to end up with 250 rows total, then use Sub TryNow2() Dim myRow As Long For myRow = Range("A65536").End(xlUp).Row To 1 Step -1 If IsNumeric(Cells(myRow, 1).Value) Then If Cells(myRow, 1).Value 1 Then Cells(myRow, 1).EntireRow.Copy Cells(myRow, 1).Resize(Cells(myRow, 1).Value - 1).EntireRow.Insert End If End If Next myRow End Sub HTH, Bernie MS Excel MVP "garlnnd" wrote in message ... Hello All, I do not work with excel on a daily basis, I'm actually a web developer but I have a request to modify an excel spreadsheet via a macro. Basically I have about 20 rows. One cell in each rows has a number ,i.e. A2 is 250 A3 is 400, etc. I need to write a macro to take that number in the A cell and copy the row that it exists in that many times. So if Cell A2 has a value of 250, I need to copy Row 2 250 times. I'm pretty sure I could figure this out if I scour the web and use trial and error, but I was wondering if someone could help me out. I dont think the macro would be terribly difficult. Thanks in advance, Garlnnd -- garlnnd ------------------------------------------------------------------------ garlnnd's Profile: http://www.excelforum.com/member.php...o&userid=27832 View this thread: http://www.excelforum.com/showthread...hreadid=473426 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with simple macro
Someone from another board nailed it with this: Dim I As Long Dim X As Long Dim rng As Range I = 2 Set rng = Range("A" & I) While rng.Value < "" X = rng.Value rng.EntireRow.Copy rng.Offset(1, 0).Resize(X - 1, 1).Insert Shift:=xlDown I = I + X Set rng = Range("A" & I) Wend Application.CutCopyMode = False Thanks for your help. -- garlnnd ------------------------------------------------------------------------ garlnnd's Profile: http://www.excelforum.com/member.php...o&userid=27832 View this thread: http://www.excelforum.com/showthread...hreadid=473426 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with simple macro
garlnnd,
I wouldn't say "nailed it" since it blows up when the value in column A is 1 or is non-numeric. Try my second version, which looks at both. HTH, Bernie MS Excel MVP "garlnnd" wrote in message ... Someone from another board nailed it with this: Dim I As Long Dim X As Long Dim rng As Range I = 2 Set rng = Range("A" & I) While rng.Value < "" X = rng.Value rng.EntireRow.Copy rng.Offset(1, 0).Resize(X - 1, 1).Insert Shift:=xlDown I = I + X Set rng = Range("A" & I) Wend Application.CutCopyMode = False Thanks for your help. -- garlnnd ------------------------------------------------------------------------ garlnnd's Profile: http://www.excelforum.com/member.php...o&userid=27832 View this thread: http://www.excelforum.com/showthread...hreadid=473426 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simple Macro help? | Excel Discussion (Misc queries) | |||
simple macro | Excel Discussion (Misc queries) | |||
Need Help With A Very Simple Macro | Excel Discussion (Misc queries) | |||
Help with simple(?) macro | Excel Programming | |||
SIMPLE MACRO!!! | Excel Programming |