Finish a list of numbers
Posted by Kristine Frafjord on September 25, 2001 12:03 AM
I have a list of numbers from 2000 to 2999.
In between there are numbers missing.
How do I detect and list the numbers missing?
Eg. 2189,2190,2192,2193.....
Missing: 2191
Posted by Nandor on September 25, 2001 4:59 AM
Here's one way (assumes your list of numbers is in Column A starting in A2) :-
Sub List_Missing_Numbers()
Dim rng As Range
Set rng = Range([A2], [A65536].End(xlUp))
Application.ScreenUpdating = False
Columns("B:C").Insert
With [B2]
.Value = 2000
.AutoFill Destination:=[B2:B2001], Type:=xlFillSeries
End With
With [C2:C2001]
.FormulaR1C1 = "=IF(ISNA(MATCH(RC[-1],R2C[-2]:R2001C[-2],0)),RC[-1],"" "")"
.Copy
.PasteSpecial Paste:=xlValues
.Sort Key1:=[C2], Order1:=xlAscending, Header:=xlNo
End With
Columns(2).Delete
Application.ScreenUpdating = True
End Sub
Posted by Nandor on September 25, 2001 5:03 AM
Correction ....
Correction :-
Sub List_Missing_Numbers()
Dim rng As Range
Set rng = Range([A2], [A65536].End(xlUp))
Application.ScreenUpdating = False
Columns("B:C").Insert
With [B2]
.Value = 2000
.AutoFill Destination:=[B2:B1001], Type:=xlFillSeries
End With
With [C2:C1001]
.FormulaR1C1 = "=IF(ISNA(MATCH(RC[-1],R2C[-2]:R1001C[-2],0)),RC[-1],"" "")"
.Copy
.PasteSpecial Paste:=xlValues
.Sort Key1:=[C2], Order1:=xlAscending, Header:=xlNo
End With
Columns(2).Delete
Application.ScreenUpdating = True
End Sub
Posted by Kristine Frafjord on September 27, 2001 2:41 AM
Re: Correction ....
Thank you.
But I'm unfortunally to slow to get this to work...I get the IFformula printed i Col:B i stead. :o)
Posted by Nandor on September 27, 2001 3:43 AM
Re: Correction ....
Sorry, but don't understand what is not working.
The macro lists in column B any numbers missing in column A from the sequence 2000 to 2999. Was this not what you wanted? Thank you.
Posted by Kristine on October 16, 2001 3:34 AM
Re: Correction ....
Posted by Kristine on October 16, 2001 3:35 AM
Re: Correction ....
The missing numbers doesn't appear - the formula appears instead in all cells of column B.
Sorry, but don't understand what is not working.
