Which numbers are missing?

  • Thread starter Thread starter GWC
  • Start date Start date
G

GWC

This an example of part of my list:

050000698
050000703

How do I get a list of the numbers that are NOT in that list? For example:

050000699
050000700
050000701
050000702

Note: My entire list contains numbers from 050000698 to 051003040
 
GWC said:
This an example of part of my list:

050000698
050000703

How do I get a list of the numbers that are NOT in that list? For example:

050000699
050000700
050000701
050000702

Note: My entire list contains numbers from 050000698 to 051003040
Click to expand...

Where will the results go? A series of cells, a VBA array, something else?
 
Auric__ has brought this to us :
Where will the results go? A series of cells, a VBA array, something else?
Click to expand...

I was thinking to dump the list into an array and iterate that for
non-consecutive numbers. When not found consecutive, subtract previous
element from current element and insert into a new 1 dim array. Then
dump the new array back into the wks where specified.

Only thing is making time to do it!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Gary,
Try this and let me know if it does what you want...


Option Explicit

Sub GetMissingNumbers()
Dim n&, k&, x&, vDataIn, vDataOut(), vAns
vDataIn = Range("a1:a12")
For n = LBound(vDataIn) To UBound(vDataIn) - 1
If Not vDataIn(n, 1) + 1 = vDataIn(n + 1, 1) Then
For k = 1 To vDataIn(n + 1, 1) - (vDataIn(n, 1) + 1)
If Not vDataIn(n, 1) + k = vDataIn(n + 1, 1) Then
ReDim Preserve vDataOut(x)
vDataOut(x) = vDataIn(n, 1) + k: x = x + 1
End If
Next 'k
End If
Next 'n
vAns = InputBox("Enter the cell address of where to start putting the
results")
With Range(vAns).Resize(UBound(vDataOut) + 1, 1)
.EntireColumn.ClearContents: .NumberFormat = "0000000000"
.Value = Application.Transpose(vDataOut)
End With
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Ron adds a good idea to autofit after dumping the results...

Sub GetMissingNumbers()
Dim n&, k&, x&, vDataIn, vDataOut(), vAns
vDataIn = Range("a1:a12")
For n = LBound(vDataIn) To UBound(vDataIn) - 1
If Not vDataIn(n, 1) + 1 = vDataIn(n + 1, 1) Then
For k = 1 To vDataIn(n + 1, 1) - (vDataIn(n, 1) + 1)
If Not vDataIn(n, 1) + k = vDataIn(n + 1, 1) Then
ReDim Preserve vDataOut(x)
vDataOut(x) = vDataIn(n, 1) + k: x = x + 1
End If
Next 'k
End If
Next 'n
vAns = InputBox("Enter the cell address of where to start putting the
results")
With Range(vAns).Resize(UBound(vDataOut) + 1, 1)
.EntireColumn.ClearContents: .NumberFormat = "0000000000"
.Value = Application.Transpose(vDataOut)
.Columns(1).AutoFit
End With
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Back
Top