| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Insert Rows Macro
Hello,
I couldn't find a macro that does this, so if anyone can point me in the right direction I would appreciate it: Sample Data: Product Code Qty ABCD AXD 2 DJFL DFY 3 Basically I want the macro to look at the quantity and recognize 2 and then insert 2 rows after ABCD, where the ABCD & AXD would be copied and the number 1 entered in the Qty for all the rows. So basically, data would then look like: Product Code Qty ABCD AXD 2 ABCD AXD 1 ABCD AXD 1 DJFL DFY 3 DJFL DFY 1 DJFL DFY 1 DJFL DFY 1 Thanks. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Insert Rows Macro
This may do what you describe, but I'm not sure it does what you want.
What happens when one of the quantities in column C is 1? Option Explicit Sub testme() Dim iRow As Long Dim HowMany As Long Dim FirstRow As Long Dim LastRow As Long Dim wks As Worksheet Set wks = ActiveSheet With wks FirstRow = 2 'headers in row 1 LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row For iRow = LastRow To FirstRow Step -1 HowMany = 0 With .Cells(iRow, "C") If IsNumeric(.Value) Then HowMany = .Value End If End With If HowMany 0 Then .Rows(iRow).Offset(1, 0).Resize(HowMany).Insert .Cells(iRow, "A").Offset(1, 0).Resize(HowMany, 2).Value _ = .Cells(iRow, "A").Resize(1, 2).Value .Cells(iRow, "C").Offset(1, 0).Resize(HowMany, 1).Value = 1 End If Next iRow End With End Sub A.S. wrote: Hello, I couldn't find a macro that does this, so if anyone can point me in the right direction I would appreciate it: Sample Data: Product Code Qty ABCD AXD 2 DJFL DFY 3 Basically I want the macro to look at the quantity and recognize 2 and then insert 2 rows after ABCD, where the ABCD & AXD would be copied and the number 1 entered in the Qty for all the rows. So basically, data would then look like: Product Code Qty ABCD AXD 2 ABCD AXD 1 ABCD AXD 1 DJFL DFY 3 DJFL DFY 1 DJFL DFY 1 DJFL DFY 1 Thanks. -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Insert Rows Macro
If it is 1 or 0, it should leave as-is. However, I tried the macro and
doesn't appear to do anything. Am I doing something wrong? Do I need to designate somewhere where to start looking? In my sheet would start on row 22. The Product is on C22, the quantity is on D22, and the code is on E22. "Dave Peterson" wrote: This may do what you describe, but I'm not sure it does what you want. What happens when one of the quantities in column C is 1? Option Explicit Sub testme() Dim iRow As Long Dim HowMany As Long Dim FirstRow As Long Dim LastRow As Long Dim wks As Worksheet Set wks = ActiveSheet With wks FirstRow = 2 'headers in row 1 LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row For iRow = LastRow To FirstRow Step -1 HowMany = 0 With .Cells(iRow, "C") If IsNumeric(.Value) Then HowMany = .Value End If End With If HowMany 0 Then .Rows(iRow).Offset(1, 0).Resize(HowMany).Insert .Cells(iRow, "A").Offset(1, 0).Resize(HowMany, 2).Value _ = .Cells(iRow, "A").Resize(1, 2).Value .Cells(iRow, "C").Offset(1, 0).Resize(HowMany, 1).Value = 1 End If Next iRow End With End Sub A.S. wrote: Hello, I couldn't find a macro that does this, so if anyone can point me in the right direction I would appreciate it: Sample Data: Product Code Qty ABCD AXD 2 DJFL DFY 3 Basically I want the macro to look at the quantity and recognize 2 and then insert 2 rows after ABCD, where the ABCD & AXD would be copied and the number 1 entered in the Qty for all the rows. So basically, data would then look like: Product Code Qty ABCD AXD 2 ABCD AXD 1 ABCD AXD 1 DJFL DFY 3 DJFL DFY 1 DJFL DFY 1 DJFL DFY 1 Thanks. -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Insert Rows Macro
Sub tst()
rw = Cells(1000, 3).End(xlUp).Row For r = rw To 22 Step -1 Cells(r, 3).Select: n = ActiveCell.Offset(0, 2).Value If n 1 Then Selection.Offset(1, 0).Resize(n, 1).EntireRow.Insert ActiveCell.Offset(1, 0).Resize(n, 1) = ActiveCell ActiveCell.Offset(1, 1).Resize(n, 1) = ActiveCell.Offset(0, 1) ActiveCell.Offset(1, 2).Resize(n, 1) = 1 End If Next End Sub "A.S." skrev: If it is 1 or 0, it should leave as-is. However, I tried the macro and doesn't appear to do anything. Am I doing something wrong? Do I need to designate somewhere where to start looking? In my sheet would start on row 22. The Product is on C22, the quantity is on D22, and the code is on E22. "Dave Peterson" wrote: This may do what you describe, but I'm not sure it does what you want. What happens when one of the quantities in column C is 1? Option Explicit Sub testme() Dim iRow As Long Dim HowMany As Long Dim FirstRow As Long Dim LastRow As Long Dim wks As Worksheet Set wks = ActiveSheet With wks FirstRow = 2 'headers in row 1 LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row For iRow = LastRow To FirstRow Step -1 HowMany = 0 With .Cells(iRow, "C") If IsNumeric(.Value) Then HowMany = .Value End If End With If HowMany 0 Then .Rows(iRow).Offset(1, 0).Resize(HowMany).Insert .Cells(iRow, "A").Offset(1, 0).Resize(HowMany, 2).Value _ = .Cells(iRow, "A").Resize(1, 2).Value .Cells(iRow, "C").Offset(1, 0).Resize(HowMany, 1).Value = 1 End If Next iRow End With End Sub A.S. wrote: Hello, I couldn't find a macro that does this, so if anyone can point me in the right direction I would appreciate it: Sample Data: Product Code Qty ABCD AXD 2 DJFL DFY 3 Basically I want the macro to look at the quantity and recognize 2 and then insert 2 rows after ABCD, where the ABCD & AXD would be copied and the number 1 entered in the Qty for all the rows. So basically, data would then look like: Product Code Qty ABCD AXD 2 ABCD AXD 1 ABCD AXD 1 DJFL DFY 3 DJFL DFY 1 DJFL DFY 1 DJFL DFY 1 Thanks. -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Insert Rows Macro
The code was written to process data in columns A:C in rows 2 to whatever.
Option Explicit ' If it is 1 or 0, it should leave as-is. However, I tried the macro and ' doesn't appear to do anything. Am I doing something wrong? Do I need to ' designate somewhere where to start looking? In my sheet would start on row ' 22. The Product is on C22, the quantity is on D22, and the code is on E22. Sub testme() Dim iRow As Long Dim HowMany As Long Dim FirstRow As Long Dim LastRow As Long Dim wks As Worksheet Set wks = ActiveSheet With wks FirstRow = 22 'headers in row 1 LastRow = .Cells(.Rows.Count, "d").End(xlUp).Row For iRow = LastRow To FirstRow Step -1 HowMany = 0 With .Cells(iRow, "d") If IsNumeric(.Value) Then HowMany = .Value End If End With If HowMany 1 Then .Rows(iRow).Offset(1, 0).Resize(HowMany).Insert .Cells(iRow, "c").Offset(1, 0).Resize(HowMany, 1).Value _ = .Cells(iRow, "c").Value .Cells(iRow, "d").Offset(1, 0).Resize(HowMany, 1).Value = 1 .Cells(iRow, "e").Offset(1, 0).Resize(HowMany, 1).Value _ = .Cells(iRow, "e").Value End If Next iRow End With End Sub A.S. wrote: If it is 1 or 0, it should leave as-is. However, I tried the macro and doesn't appear to do anything. Am I doing something wrong? Do I need to designate somewhere where to start looking? In my sheet would start on row 22. The Product is on C22, the quantity is on D22, and the code is on E22. "Dave Peterson" wrote: This may do what you describe, but I'm not sure it does what you want. What happens when one of the quantities in column C is 1? Option Explicit Sub testme() Dim iRow As Long Dim HowMany As Long Dim FirstRow As Long Dim LastRow As Long Dim wks As Worksheet Set wks = ActiveSheet With wks FirstRow = 2 'headers in row 1 LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row For iRow = LastRow To FirstRow Step -1 HowMany = 0 With .Cells(iRow, "C") If IsNumeric(.Value) Then HowMany = .Value End If End With If HowMany 0 Then .Rows(iRow).Offset(1, 0).Resize(HowMany).Insert .Cells(iRow, "A").Offset(1, 0).Resize(HowMany, 2).Value _ = .Cells(iRow, "A").Resize(1, 2).Value .Cells(iRow, "C").Offset(1, 0).Resize(HowMany, 1).Value = 1 End If Next iRow End With End Sub A.S. wrote: Hello, I couldn't find a macro that does this, so if anyone can point me in the right direction I would appreciate it: Sample Data: Product Code Qty ABCD AXD 2 DJFL DFY 3 Basically I want the macro to look at the quantity and recognize 2 and then insert 2 rows after ABCD, where the ABCD & AXD would be copied and the number 1 entered in the Qty for all the rows. So basically, data would then look like: Product Code Qty ABCD AXD 2 ABCD AXD 1 ABCD AXD 1 DJFL DFY 3 DJFL DFY 1 DJFL DFY 1 DJFL DFY 1 Thanks. -- Dave Peterson -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Insert Rows Macro
I moved everything over to columns A:C but still does not do anything? Any
thoughts to why? "Dave Peterson" wrote: The code was written to process data in columns A:C in rows 2 to whatever. Option Explicit ' If it is 1 or 0, it should leave as-is. However, I tried the macro and ' doesn't appear to do anything. Am I doing something wrong? Do I need to ' designate somewhere where to start looking? In my sheet would start on row ' 22. The Product is on C22, the quantity is on D22, and the code is on E22. Sub testme() Dim iRow As Long Dim HowMany As Long Dim FirstRow As Long Dim LastRow As Long Dim wks As Worksheet Set wks = ActiveSheet With wks FirstRow = 22 'headers in row 1 LastRow = .Cells(.Rows.Count, "d").End(xlUp).Row For iRow = LastRow To FirstRow Step -1 HowMany = 0 With .Cells(iRow, "d") If IsNumeric(.Value) Then HowMany = .Value End If End With If HowMany 1 Then .Rows(iRow).Offset(1, 0).Resize(HowMany).Insert .Cells(iRow, "c").Offset(1, 0).Resize(HowMany, 1).Value _ = .Cells(iRow, "c").Value .Cells(iRow, "d").Offset(1, 0).Resize(HowMany, 1).Value = 1 .Cells(iRow, "e").Offset(1, 0).Resize(HowMany, 1).Value _ = .Cells(iRow, "e").Value End If Next iRow End With End Sub A.S. wrote: If it is 1 or 0, it should leave as-is. However, I tried the macro and doesn't appear to do anything. Am I doing something wrong? Do I need to designate somewhere where to start looking? In my sheet would start on row 22. The Product is on C22, the quantity is on D22, and the code is on E22. "Dave Peterson" wrote: This may do what you describe, but I'm not sure it does what you want. What happens when one of the quantities in column C is 1? Option Explicit Sub testme() Dim iRow As Long Dim HowMany As Long Dim FirstRow As Long Dim LastRow As Long Dim wks As Worksheet Set wks = ActiveSheet With wks FirstRow = 2 'headers in row 1 LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row For iRow = LastRow To FirstRow Step -1 HowMany = 0 With .Cells(iRow, "C") If IsNumeric(.Value) Then HowMany = .Value End If End With If HowMany 0 Then .Rows(iRow).Offset(1, 0).Resize(HowMany).Insert .Cells(iRow, "A").Offset(1, 0).Resize(HowMany, 2).Value _ = .Cells(iRow, "A").Resize(1, 2).Value .Cells(iRow, "C").Offset(1, 0).Resize(HowMany, 1).Value = 1 End If Next iRow End With End Sub A.S. wrote: Hello, I couldn't find a macro that does this, so if anyone can point me in the right direction I would appreciate it: Sample Data: Product Code Qty ABCD AXD 2 DJFL DFY 3 Basically I want the macro to look at the quantity and recognize 2 and then insert 2 rows after ABCD, where the ABCD & AXD would be copied and the number 1 entered in the Qty for all the rows. So basically, data would then look like: Product Code Qty ABCD AXD 2 ABCD AXD 1 ABCD AXD 1 DJFL DFY 3 DJFL DFY 1 DJFL DFY 1 DJFL DFY 1 Thanks. -- Dave Peterson -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Insert Rows Macro
I changed the macro and reposted according to your requirements.
If you used the first version, it looks in A2:C###. A.S. wrote: I moved everything over to columns A:C but still does not do anything? Any thoughts to why? "Dave Peterson" wrote: The code was written to process data in columns A:C in rows 2 to whatever. Option Explicit ' If it is 1 or 0, it should leave as-is. However, I tried the macro and ' doesn't appear to do anything. Am I doing something wrong? Do I need to ' designate somewhere where to start looking? In my sheet would start on row ' 22. The Product is on C22, the quantity is on D22, and the code is on E22. Sub testme() Dim iRow As Long Dim HowMany As Long Dim FirstRow As Long Dim LastRow As Long Dim wks As Worksheet Set wks = ActiveSheet With wks FirstRow = 22 'headers in row 1 LastRow = .Cells(.Rows.Count, "d").End(xlUp).Row For iRow = LastRow To FirstRow Step -1 HowMany = 0 With .Cells(iRow, "d") If IsNumeric(.Value) Then HowMany = .Value End If End With If HowMany 1 Then .Rows(iRow).Offset(1, 0).Resize(HowMany).Insert .Cells(iRow, "c").Offset(1, 0).Resize(HowMany, 1).Value _ = .Cells(iRow, "c").Value .Cells(iRow, "d").Offset(1, 0).Resize(HowMany, 1).Value = 1 .Cells(iRow, "e").Offset(1, 0).Resize(HowMany, 1).Value _ = .Cells(iRow, "e").Value End If Next iRow End With End Sub A.S. wrote: If it is 1 or 0, it should leave as-is. However, I tried the macro and doesn't appear to do anything. Am I doing something wrong? Do I need to designate somewhere where to start looking? In my sheet would start on row 22. The Product is on C22, the quantity is on D22, and the code is on E22. "Dave Peterson" wrote: This may do what you describe, but I'm not sure it does what you want. What happens when one of the quantities in column C is 1? Option Explicit Sub testme() Dim iRow As Long Dim HowMany As Long Dim FirstRow As Long Dim LastRow As Long Dim wks As Worksheet Set wks = ActiveSheet With wks FirstRow = 2 'headers in row 1 LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row For iRow = LastRow To FirstRow Step -1 HowMany = 0 With .Cells(iRow, "C") If IsNumeric(.Value) Then HowMany = .Value End If End With If HowMany 0 Then .Rows(iRow).Offset(1, 0).Resize(HowMany).Insert .Cells(iRow, "A").Offset(1, 0).Resize(HowMany, 2).Value _ = .Cells(iRow, "A").Resize(1, 2).Value .Cells(iRow, "C").Offset(1, 0).Resize(HowMany, 1).Value = 1 End If Next iRow End With End Sub A.S. wrote: Hello, I couldn't find a macro that does this, so if anyone can point me in the right direction I would appreciate it: Sample Data: Product Code Qty ABCD AXD 2 DJFL DFY 3 Basically I want the macro to look at the quantity and recognize 2 and then insert 2 rows after ABCD, where the ABCD & AXD would be copied and the number 1 entered in the Qty for all the rows. So basically, data would then look like: Product Code Qty ABCD AXD 2 ABCD AXD 1 ABCD AXD 1 DJFL DFY 3 DJFL DFY 1 DJFL DFY 1 DJFL DFY 1 Thanks. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Insert Rows Macro
Thanks Dave, this worked great!
"Dave Peterson" wrote: I changed the macro and reposted according to your requirements. If you used the first version, it looks in A2:C###. A.S. wrote: I moved everything over to columns A:C but still does not do anything? Any thoughts to why? "Dave Peterson" wrote: The code was written to process data in columns A:C in rows 2 to whatever. Option Explicit ' If it is 1 or 0, it should leave as-is. However, I tried the macro and ' doesn't appear to do anything. Am I doing something wrong? Do I need to ' designate somewhere where to start looking? In my sheet would start on row ' 22. The Product is on C22, the quantity is on D22, and the code is on E22. Sub testme() Dim iRow As Long Dim HowMany As Long Dim FirstRow As Long Dim LastRow As Long Dim wks As Worksheet Set wks = ActiveSheet With wks FirstRow = 22 'headers in row 1 LastRow = .Cells(.Rows.Count, "d").End(xlUp).Row For iRow = LastRow To FirstRow Step -1 HowMany = 0 With .Cells(iRow, "d") If IsNumeric(.Value) Then HowMany = .Value End If End With If HowMany 1 Then .Rows(iRow).Offset(1, 0).Resize(HowMany).Insert .Cells(iRow, "c").Offset(1, 0).Resize(HowMany, 1).Value _ = .Cells(iRow, "c").Value .Cells(iRow, "d").Offset(1, 0).Resize(HowMany, 1).Value = 1 .Cells(iRow, "e").Offset(1, 0).Resize(HowMany, 1).Value _ = .Cells(iRow, "e").Value End If Next iRow End With End Sub A.S. wrote: If it is 1 or 0, it should leave as-is. However, I tried the macro and doesn't appear to do anything. Am I doing something wrong? Do I need to designate somewhere where to start looking? In my sheet would start on row 22. The Product is on C22, the quantity is on D22, and the code is on E22. "Dave Peterson" wrote: This may do what you describe, but I'm not sure it does what you want. What happens when one of the quantities in column C is 1? Option Explicit Sub testme() Dim iRow As Long Dim HowMany As Long Dim FirstRow As Long Dim LastRow As Long Dim wks As Worksheet Set wks = ActiveSheet With wks FirstRow = 2 'headers in row 1 LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row For iRow = LastRow To FirstRow Step -1 HowMany = 0 With .Cells(iRow, "C") If IsNumeric(.Value) Then HowMany = .Value End If End With If HowMany 0 Then .Rows(iRow).Offset(1, 0).Resize(HowMany).Insert .Cells(iRow, "A").Offset(1, 0).Resize(HowMany, 2).Value _ = .Cells(iRow, "A").Resize(1, 2).Value .Cells(iRow, "C").Offset(1, 0).Resize(HowMany, 1).Value = 1 End If Next iRow End With End Sub A.S. wrote: Hello, I couldn't find a macro that does this, so if anyone can point me in the right direction I would appreciate it: Sample Data: Product Code Qty ABCD AXD 2 DJFL DFY 3 Basically I want the macro to look at the quantity and recognize 2 and then insert 2 rows after ABCD, where the ABCD & AXD would be copied and the number 1 entered in the Qty for all the rows. So basically, data would then look like: Product Code Qty ABCD AXD 2 ABCD AXD 1 ABCD AXD 1 DJFL DFY 3 DJFL DFY 1 DJFL DFY 1 DJFL DFY 1 Thanks. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel Macro to insert rows | Excel Discussion (Misc queries) | |||
| how do I create a macro to auto insert rows? | Excel Worksheet Functions | |||
| asking again, macro to insert rows | Excel Worksheet Functions | |||
| insert rows in excel spreadsheet via macro | Excel Worksheet Functions | |||
| Excel Macro to insert rows in a list | Excel Worksheet Functions | |||
Linear Mode
