| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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''. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Insert Name missing from Toolbar | Excel Discussion (Misc queries) | |||
| to find missing serial numbers in randomly generated numbers | Excel Worksheet Functions | |||
| View & Insert missing from menu bar!?!?! | Excel Discussion (Misc queries) | |||
| What variable do you insert for X-cel if there is missing data? | New Users to Excel | |||
| Macro To insert missing data | Excel Programming | |||
Linear Mode
