Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting row based on value in cell in Column C
Hi,
I need a macro that will take values in column C and add that number of rows below that cell as the number in that cell. An example: A cell in C2 for instance has the value 3. I want the macro to then read the 3 in cell C2 and then add 3 rows below cell C2 or row 2. Please help. This is like a two step process that I need help with. Thank you. Anonymous Chief |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting row based on value in cell in Column C
Not sure if the IsNumeric actually helps that much but
here is some code that will work Sub InsertRows() For Each cell In Range("C:C") If IsNumeric(cell.Value) And cell.Value = 1 Then Range(cell.Offset(1, 0).EntireRow, _ cell.Offset(cell.Value, 0).EntireRow).Insert xlDown End If Next cell End Sub Cheers -----Original Message----- Hi, I need a macro that will take values in column C and add that number of rows below that cell as the number in that cell. An example: A cell in C2 for instance has the value 3. I want the macro to then read the 3 in cell C2 and then add 3 rows below cell C2 or row 2. Please help. This is like a two step process that I need help with. Thank you. Anonymous Chief . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting row based on value in cell in Column C
Hi richard,
Thank you for the code. I tried it but I get a compile error and the visualbasic editor highlights "xlDown" in blue, and then highlights "Sub InsertRows()" in yellow. I know you are onto something real good here. Please help. Later "richardreye" wrote in message ... Not sure if the IsNumeric actually helps that much but here is some code that will work Sub InsertRows() For Each cell In Range("C:C") If IsNumeric(cell.Value) And cell.Value = 1 Then Range(cell.Offset(1, 0).EntireRow, _ cell.Offset(cell.Value, 0).EntireRow).Insert xlDown End If Next cell End Sub Cheers -----Original Message----- Hi, I need a macro that will take values in column C and add that number of rows below that cell as the number in that cell. An example: A cell in C2 for instance has the value 3. I want the macro to then read the 3 in cell C2 and then add 3 rows below cell C2 or row 2. Please help. This is like a two step process that I need help with. Thank you. Anonymous Chief . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting row based on value in cell in Column C
Sub InsertRows()
Dim lastRow as Long, cell as Range Dim i as Long lastrow = cells(rows.count,"C").End(xlup).row + 1 for i = lastrow to 2 step -1 set cell = cells(i,"C") If IsNumeric(cell(0,1).Value) Then If cell(0,1).Value = 1 Then cell.Resize(cells(0,1).Value) _ .EntireRow.Insert End if End If Next cell End Sub -- Regards, Tom Ogilvy "Anonymous Chief" wrote in message . .. Hi richard, Thank you for the code. I tried it but I get a compile error and the visualbasic editor highlights "xlDown" in blue, and then highlights "Sub InsertRows()" in yellow. I know you are onto something real good here. Please help. Later "richardreye" wrote in message ... Not sure if the IsNumeric actually helps that much but here is some code that will work Sub InsertRows() For Each cell In Range("C:C") If IsNumeric(cell.Value) And cell.Value = 1 Then Range(cell.Offset(1, 0).EntireRow, _ cell.Offset(cell.Value, 0).EntireRow).Insert xlDown End If Next cell End Sub Cheers -----Original Message----- Hi, I need a macro that will take values in column C and add that number of rows below that cell as the number in that cell. An example: A cell in C2 for instance has the value 3. I want the macro to then read the 3 in cell C2 and then add 3 rows below cell C2 or row 2. Please help. This is like a two step process that I need help with. Thank you. Anonymous Chief . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting row based on value in cell in Column C
Richard's solution is testing the original and the inserted
rows in Column C. Since the inserted rows do not have a value it works but it would be much better to use to use Step -1 and start from the bottom and work up. Checking inserted rows is not the most efficient. And the test for numeric that he questioned if it would be needed. -- David McRitchie |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting row based on value in cell in Column C
I tried Tom's solution, but it also does not run, prompts me to debug. Guys,
you have to know that I am not that familiar with creating macros. I can only insert the code in the visual basic editor and run it, but am nt capable of debugging it. Tom and Richard have been great, but it still does not work. I don't know if it will help. I am using Excel 2003. Please help me, and thank you all for your input. I guess if smeone could try out somthing based on David McRitchie's suggestion. Thanks "David McRitchie" wrote in message ... Richard's solution is testing the original and the inserted rows in Column C. Since the inserted rows do not have a value it works but it would be much better to use to use Step -1 and start from the bottom and work up. Checking inserted rows is not the most efficient. And the test for numeric that he questioned if it would be needed. -- David McRitchie |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting row based on value in cell in Column C
Tom gave you a complete macro that includes what I said,
what you see in the thread in chronological order is not necessarily the order that it "should be read" [or even ignored for something more complete]. There were a couple of typos (if it were my code they'd be errors and I'd say typos) Sub InsertRows() Dim lastRow As Long, cell As Range 'Tom Ogilvy, 2005-03-09 programming --corrected Dim i As Long lastRow = Cells(Rows.Count, "C").End(xlUp).row + 1 For i = lastRow To 2 Step -1 Set cell = Cells(i, "C") If IsNumeric(cell(0, 1).Value) Then '-- correction If cell(0, 1).Value = 1 Then cell.Resize(cell(0, 1).Value) _ .EntireRow.Insert End If End If Next i '--correction End Sub --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm "Anonymous Chief" wrote... I tried Tom's solution, but it also does not run, prompts me to debug. Guys, you have to know that I am not that familiar with creating macros. I can only insert the code in the visual basic editor and run it, but am nt capable of debugging it. Tom and Richard have been great, but it still does not work. I don't know if it will help. I am using Excel 2003. Please help me, and thank you all for your input. I guess if smeone could try out somthing based on David McRitchie's suggestion. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
inserting a number from a particular cell based on if/thens | Excel Discussion (Misc queries) | |||
need help please inserting multiple rows based on cell value then copying to data sheet | Excel Worksheet Functions | |||
Inserting picture into excel based on # in the another cell | Excel Worksheet Functions | |||
inserting a named range into new cells based on a named cell | Excel Discussion (Misc queries) | |||
Deleting Rows based on text in cell & formatting cell based on text in column beside it | Excel Programming |