Number Patterns and creating fill-ins

[DavidForner]

Question regarding number patterns.

I have a list of numbers, ranging from 220-1000 (or 2000), each with an accompanying value. However, there are times in the 220-1000 where a number will be missing. For example, along the 220-1000, the string of numbers may go 400,401,402,404,405 (and each of these would have a corresponding value with it). Here, 403 is missing. Is there any way to have excel recognize that 403 is missing, insert the 403, and give it a corresponding value of 0 with it?

This would need to be repeated for all missing numbers between 220-1000 (or 2000, should I need to use 2000).

This set of numbers has been copy and pasted from another program. Therefore, when pasting into excel, column A would be the number from 220-1000, and Column B would be the accompanying value. I would want excel to insert the missing number into Column A (at the appropriate location) and the 0 value into Column B (or Columns C/D, E/F, etc, so that they are still paired, and in proper order).

Thanks for any help!

 

[DavidForner]

Bump

 

[DavidForner]

Quick moving board.

At the moment I've used an IF statement to address if the next number is the previous + 1, if it is, it fills as 0 and if it isn't, it fills as previous + 1. This is fairly tedious, however. Any easier ways?

 

[MickG]

Try this:-

[COLOR=navy]Sub[/COLOR] MG14Jul47
[COLOR=navy]Dim[/COLOR] Lst [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] Rw
Lst = Range("A" & Rows.Count).End(xlUp).Row
[COLOR=navy]For[/COLOR] Rw = Lst To 2 [COLOR=navy]Step[/COLOR] -1
    [COLOR=navy]With[/COLOR] Range("A" & Rw)
        [COLOR=navy]If[/COLOR] Not .Value = .Offset(-1).Value + 1 [COLOR=navy]Then[/COLOR]
            .EntireRow.Insert
            .Offset(-1).Value = .Value - 1
            .Offset(-1, 1) = 0
            Rw = Rw + 1
       [COLOR=navy]End[/COLOR] If
 [COLOR=navy]End[/COLOR] With
 [COLOR=navy]Next[/COLOR] Rw
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[DavidForner]

Worked brilliantly. Thanks a lot.

 

[DavidForner]

Have another problem, very similar (nearly exact) to the previous.

I now have a data set, ranging from ~500-1000, but each has 3 decimal points (therefore, for example, 550.331). Would it be possible for the previous macro to be altered so that it will fill every decimal place? Does excel even have enough room to do such things?

I have never written a macro, and so I have attempted to alter the previous. It doesn't seem to be working, but I'm unsure if I'm altering it incorrectly or just don't have the computing capacity.

Thanks again!

 

[DavidForner]

Another bump

 

[MickG]

Try this
Nb:- This should for cater for number ~500~1000, be careful you don't run of the page

Dim Lst As Long, Rw
Const num = 0.001
Application.ScreenUpdating = False
Lst = Range("A" & Rows.Count).End(xlUp).Row
For Rw = Lst To 2 Step -1
    With Range("A" & Rw)
        If Not .Value + 10000 = .Offset(-1).Value + num + 10000 Then
            .EntireRow.Insert
            .Offset(-1).Value = .Value - num
            .Offset(-1, 1) = 0
            Rw = Rw + 1
         End If
 End With
 Next Rw
Application.ScreenUpdating = True
End Sub

Mick