|
achieving sequential numbering
Hello,
I am looking for a macro or other solution which can loop through my worksheet. The worksheet looks as follows: Branch 1 240230 x $22 240239 y $25 240240 z $26 Totals Branch 2 240230 x $30 240231 a $58 240232 b $22 240241 c $18 Totals Branch 3 240229 d $20 240231 a $22 240232 b $23 240240 z $22 Totals I would like it to compare the value of the number in column A with that of the number above/below it. If there is a difference of more than 1, I would like it to insert a row with that number, so that eventually all the numbers are sequential. The inserted row should only have the number, no other values in that row. There should be a minimum (240229) and a maximum (240241) found for all values in column A of sheet 1 and everything in between there for each branch should be filled in. For example: Branch 1 240229 240230 x $22 240231 240232 240233 240234 240235 240236 240237 240238 240239 y $25 240240 z $26 240241 Totals Branch 2 240229 240230 x $30 240231 a $58 240232 b $22 240233 240234 240235 240236 240237 240238 240239 240240 240241 c $18 Totals Branch 3 240229 d $20 240230 240231 a $22 240232 b $23 240233 240234 240235 240236 240237 240238 240239 240240 z $22 240241 Totals I tried this a different way before, but now I would like to approach it slightly differently: At the start of the macro, I would an inputbox to appear which asks me for the min and max values (for example, min=240229 and max=240241). Then, for as long as there are values, I would like it to loop through and insert rows in between until everything is sequential between the min and max values. Even if the last number in the sequence is 240240, I still need it to insert 240241. The most efficient way to achieve this seems to be by using the offset function, to step (max-min) +2 rows down and start over again, instead of looking for the branch number. Unfortunately- whatever I try doesn't work. Any help would be greatly appreciated! |
achieving sequential numbering
Please try this.///If you are new to macros set the Security level to
low/medium in (Tools|Macro|Security). 'Launch VBE using short-key Alt+F11. Insert a module and paste the below code. Save. Get back to Workbook. Tools|Macro|Run Macro() Sub Macro() Dim lngRow Dim lngTemp lngRow = 1 Do While Range("A" & lngRow) < "" If Range("A" & lngRow) Like ["######"] Then If Range("A" & lngRow) < lngTemp Then Rows(lngRow).Insert Range("A" & lngRow) = lngTemp End If lngTemp = lngTemp + 1 Else lngTemp = 240229 End If lngRow = lngRow + 1 Loop End Sub -- If this post helps click Yes --------------- Jacob Skaria "PVT" wrote: Hello, I am looking for a macro or other solution which can loop through my worksheet. The worksheet looks as follows: Branch 1 240230 x $22 240239 y $25 240240 z $26 Totals Branch 2 240230 x $30 240231 a $58 240232 b $22 240241 c $18 Totals Branch 3 240229 d $20 240231 a $22 240232 b $23 240240 z $22 Totals I would like it to compare the value of the number in column A with that of the number above/below it. If there is a difference of more than 1, I would like it to insert a row with that number, so that eventually all the numbers are sequential. The inserted row should only have the number, no other values in that row. There should be a minimum (240229) and a maximum (240241) found for all values in column A of sheet 1 and everything in between there for each branch should be filled in. For example: Branch 1 240229 240230 x $22 240231 240232 240233 240234 240235 240236 240237 240238 240239 y $25 240240 z $26 240241 Totals Branch 2 240229 240230 x $30 240231 a $58 240232 b $22 240233 240234 240235 240236 240237 240238 240239 240240 240241 c $18 Totals Branch 3 240229 d $20 240230 240231 a $22 240232 b $23 240233 240234 240235 240236 240237 240238 240239 240240 z $22 240241 Totals I tried this a different way before, but now I would like to approach it slightly differently: At the start of the macro, I would an inputbox to appear which asks me for the min and max values (for example, min=240229 and max=240241). Then, for as long as there are values, I would like it to loop through and insert rows in between until everything is sequential between the min and max values. Even if the last number in the sequence is 240240, I still need it to insert 240241. The most efficient way to achieve this seems to be by using the offset function, to step (max-min) +2 rows down and start over again, instead of looking for the branch number. Unfortunately- whatever I try doesn't work. Any help would be greatly appreciated! |
achieving sequential numbering
A small correction..
Sub Macro() Dim lngRow Dim lngTemp lngRow = 1 Do While Range("A" & lngRow) < "" If Range("A" & lngRow) Like ["######"] Then If Range("A" & lngRow) < lngTemp Then Rows(lngRow).Insert Range("A" & lngRow) = lngTemp End If lngTemp = lngTemp + 1 Else If lngTemp 240229 And lngTemp < 240242 Then Rows(lngRow).Insert Range("A" & lngRow) = lngTemp lngTemp = lngTemp + 1 End If lngTemp = 240229 End If lngRow = lngRow + 1 Loop End Sub If this post helps click Yes --------------- Jacob Skaria "PVT" wrote: Hello, I am looking for a macro or other solution which can loop through my worksheet. The worksheet looks as follows: Branch 1 240230 x $22 240239 y $25 240240 z $26 Totals Branch 2 240230 x $30 240231 a $58 240232 b $22 240241 c $18 Totals Branch 3 240229 d $20 240231 a $22 240232 b $23 240240 z $22 Totals I would like it to compare the value of the number in column A with that of the number above/below it. If there is a difference of more than 1, I would like it to insert a row with that number, so that eventually all the numbers are sequential. The inserted row should only have the number, no other values in that row. There should be a minimum (240229) and a maximum (240241) found for all values in column A of sheet 1 and everything in between there for each branch should be filled in. For example: Branch 1 240229 240230 x $22 240231 240232 240233 240234 240235 240236 240237 240238 240239 y $25 240240 z $26 240241 Totals Branch 2 240229 240230 x $30 240231 a $58 240232 b $22 240233 240234 240235 240236 240237 240238 240239 240240 240241 c $18 Totals Branch 3 240229 d $20 240230 240231 a $22 240232 b $23 240233 240234 240235 240236 240237 240238 240239 240240 z $22 240241 Totals I tried this a different way before, but now I would like to approach it slightly differently: At the start of the macro, I would an inputbox to appear which asks me for the min and max values (for example, min=240229 and max=240241). Then, for as long as there are values, I would like it to loop through and insert rows in between until everything is sequential between the min and max values. Even if the last number in the sequence is 240240, I still need it to insert 240241. The most efficient way to achieve this seems to be by using the offset function, to step (max-min) +2 rows down and start over again, instead of looking for the branch number. Unfortunately- whatever I try doesn't work. Any help would be greatly appreciated! |
achieving sequential numbering
That is awesome! It works like a charm!!!
How could I modify it if there would be an empty row in between 240241 (max) and the 240229 (the next min), to skip that empty row and go on? |
achieving sequential numbering
Yesterday it was a bit late....Please find the below modified code to handle
blank entries. You can change the while condition if you have more than 1 blanks inbetween. If this post helps click Yes --------------- Jacob Skaria Dim lngRow Dim lngTemp Dim lngBlank lngRow = 1 Do If Range("A" & lngRow) Like ["######"] Then If Range("A" & lngRow) < lngTemp Then Rows(lngRow).Insert Range("A" & lngRow) = lngTemp End If lngTemp = lngTemp + 1 lngBlank = 0 ElseIf Trim(Range("A" & lngRow)) < "" Then If lngTemp 240229 And lngTemp < 240242 Then Rows(lngRow).Insert Range("A" & lngRow) = lngTemp lngTemp = lngTemp + 1 End If lngTemp = 240229 lngBlank = 0 Else lngBlank = lngBlank + 1 End If lngRow = lngRow + 1 Loop While lngBlank < 2 |
| All times are GMT +1. The time now is 06:21 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com