|
Insert Missing Numbers
I used to have a macro that inserted rows, between numbers, and inserted the
missing numbers as it looped down the list. Can't find it now... Basically, if I have the following setup: A1 = 1 A2 = 8 A3 = 10 I want to see: A1 = 1 A2 = 2 A3 = 3 .... A9 = 9 A10 = 10 How can I do this? Thanks, Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
Insert Missing Numbers
This is crude but will do it for you! Code: -------------------- Dim Rng As Range, MyCell As Range Dim MyNum As Double, i As Long Set Rng = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row) With Rng MyNum = Application.WorksheetFunction.Large(Rng, 1) End With For i = MyNum To 1 Step -1 Cells(i, 1) = MyNum MyNum = MyNum - 1 Next i -------------------- ryguy7272;523377 Wrote: I used to have a macro that inserted rows, between numbers, and inserted the missing numbers as it looped down the list. Can't find it now... Basically, if I have the following setup: A1 = 1 A2 = 8 A3 = 10 I want to see: A1 = 1 A2 = 2 A3 = 3 .... A9 = 9 A10 = 10 How can I do this? Thanks, Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. -- Simon Lloyd Regards, Simon Lloyd 'Microsoft Office Help' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=143638 |
Insert Missing Numbers
That works great!!
Thanks, Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Simon Lloyd" wrote: This is crude but will do it for you! Code: -------------------- Dim Rng As Range, MyCell As Range Dim MyNum As Double, i As Long Set Rng = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row) With Rng MyNum = Application.WorksheetFunction.Large(Rng, 1) End With For i = MyNum To 1 Step -1 Cells(i, 1) = MyNum MyNum = MyNum - 1 Next i -------------------- ryguy7272;523377 Wrote: I used to have a macro that inserted rows, between numbers, and inserted the missing numbers as it looped down the list. Can't find it now... Basically, if I have the following setup: A1 = 1 A2 = 8 A3 = 10 I want to see: A1 = 1 A2 = 2 A3 = 3 .... A9 = 9 A10 = 10 How can I do this? Thanks, Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. -- Simon Lloyd Regards, Simon Lloyd 'Microsoft Office Help' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=143638 |
Insert Missing Numbers
It's not brilliant, it simply finds the largest number in the range using a worksheet function and then numbers backwards from that cell. Glad you're sorted! ryguy7272;523493 Wrote: That works great!! Thanks, Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Simon Lloyd" wrote: This is crude but will do it for you! Code: -------------------- Dim Rng As Range, MyCell As Range Dim MyNum As Double, i As Long Set Rng = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row) With Rng MyNum = Application.WorksheetFunction.Large(Rng, 1) End With For i = MyNum To 1 Step -1 Cells(i, 1) = MyNum MyNum = MyNum - 1 Next i -------------------- ryguy7272;523377 Wrote: I used to have a macro that inserted rows, between numbers, and inserted the missing numbers as it looped down the list. Can't find it now... Basically, if I have the following setup: A1 = 1 A2 = 8 A3 = 10 I want to see: A1 = 1 A2 = 2 A3 = 3 .... A9 = 9 A10 = 10 How can I do this? Thanks, Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. -- Simon Lloyd Regards, Simon Lloyd 'Microsoft Office Help' ('The Code Cage - Microsoft Office Help - Microsoft Office Discussion' (http://www.thecodecage.com)) ------------------------------------------------------------------------ Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1) View this thread: 'Insert Missing Numbers - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=143638) -- Simon Lloyd Regards, Simon Lloyd 'Microsoft Office Help' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=143638 |
Insert Missing Numbers
Ryan/Simon
You can avoid that looping using this one liner....With data starting from cell A1 Range("A1").DataSeries Rowcol:=xlColumns, Type:=xlLinear, _ Step:=1, Stop:=WorksheetFunction.Max(Columns(1)) If this post helps click Yes --------------- Jacob Skaria "Simon Lloyd" wrote: It's not brilliant, it simply finds the largest number in the range using a worksheet function and then numbers backwards from that cell. Glad you're sorted! ryguy7272;523493 Wrote: That works great!! Thanks, Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Simon Lloyd" wrote: This is crude but will do it for you! Code: -------------------- Dim Rng As Range, MyCell As Range Dim MyNum As Double, i As Long Set Rng = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row) With Rng MyNum = Application.WorksheetFunction.Large(Rng, 1) End With For i = MyNum To 1 Step -1 Cells(i, 1) = MyNum MyNum = MyNum - 1 Next i -------------------- ryguy7272;523377 Wrote: I used to have a macro that inserted rows, between numbers, and inserted the missing numbers as it looped down the list. Can't find it now... Basically, if I have the following setup: A1 = 1 A2 = 8 A3 = 10 I want to see: A1 = 1 A2 = 2 A3 = 3 .... A9 = 9 A10 = 10 How can I do this? Thanks, Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. -- Simon Lloyd Regards, Simon Lloyd 'Microsoft Office Help' ('The Code Cage - Microsoft Office Help - Microsoft Office Discussion' (http://www.thecodecage.com)) ------------------------------------------------------------------------ Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1) View this thread: 'Insert Missing Numbers - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=143638) -- Simon Lloyd Regards, Simon Lloyd 'Microsoft Office Help' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=143638 |
Insert Missing Numbers
Errrrmmmm!, BRILLIANT! :) Jacob Skaria;523549 Wrote: Ryan/Simon You can avoid that looping using this one liner....With data starting from cell A1 Range("A1").DataSeries Rowcol:=xlColumns, Type:=xlLinear, _ Step:=1, Stop:=WorksheetFunction.Max(Columns(1)) If this post helps click Yes --------------- Jacob Skaria "Simon Lloyd" wrote: It's not brilliant, it simply finds the largest number in the range using a worksheet function and then numbers backwards from that cell. Glad you're sorted! ryguy7272;523493 Wrote: That works great!! Thanks, Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Simon Lloyd" wrote: This is crude but will do it for you! Code: -------------------- Dim Rng As Range, MyCell As Range Dim MyNum As Double, i As Long Set Rng = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row) With Rng MyNum = Application.WorksheetFunction.Large(Rng, 1) End With For i = MyNum To 1 Step -1 Cells(i, 1) = MyNum MyNum = MyNum - 1 Next i -------------------- ryguy7272;523377 Wrote: I used to have a macro that inserted rows, between numbers, and inserted the missing numbers as it looped down the list. Can't find it now... Basically, if I have the following setup: A1 = 1 A2 = 8 A3 = 10 I want to see: A1 = 1 A2 = 2 A3 = 3 .... A9 = 9 A10 = 10 How can I do this? Thanks, Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. -- Simon Lloyd Regards, Simon Lloyd 'Microsoft Office Help' ('The Code Cage - Microsoft Office Help - Microsoft Office Discussion' ('The Code Cage - Microsoft Office Help - Microsoft Office Discussion' (http://www.thecodecage.com))) ------------------------------------------------------------------------ Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon Lloyd' ('The Code Cage Forums - View Profile: Simon Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)) View this thread: 'Insert Missing Numbers - The Code Cage Forums' ('Insert Missing Numbers - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh....php?t=143638)) -- Simon Lloyd Regards, Simon Lloyd 'Microsoft Office Help' ('The Code Cage - Microsoft Office Help - Microsoft Office Discussion' (http://www.thecodecage.com)) ------------------------------------------------------------------------ Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1) View this thread: 'Insert Missing Numbers - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=143638) -- Simon Lloyd Regards, Simon Lloyd 'Microsoft Office Help' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=143638 |
| All times are GMT +1. The time now is 07:23 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com