Ok, let's try a small sample.

Assume you have a list of numbers in the range A1:A10. Those numbers a

2, 5, 7, 8, 10, 12, 15, 16, 17, 19

You want to find what numbers are missing from that list based on the
sequence of 1 to 20.

In the macro, where you see <<<<, those are the portions that you would need
to change based on the criteria. So, the sample criteria a the list of
numbers is in the range A1:A10 and the sequence to test is 1 to 20. 1 is the
lower boundary and 20 is the upper boundary. The output of the macro will
start in D1. Here's the macro with those criteria:

Sub FindMissing()
Dim lngUpper As Long
Dim lngLower As Long
Dim i As Long
Dim rngData As Range
Dim lngcount As Long

Set rngData = Range("A1:A10") '<<<<change as needed
lngLower = 1 '<<<<start of sequence
lngUpper = 20 '<<<<end of sequence
lngcount = 1

For i = lngLower To lngUpper
If Not IsNumeric(Application.Match(i, _
rngData, 0)) Then
Range("D" & lngcount).Value = i '<<<<sets output to column D
lngcount = lngcount + 1
End If
Next i

End Sub

Now, to run this macro:

Hit ALT F11 (hold down the ALT key then press function key F11)

This will open the VBA editor.

Goto the menu ViewProject Explorer (or hit CTRL R - hold down the CTRL key
then press R)

In the pane that opens find your file. It will look like this:

VBAProject(your_file_name)

Right click on your file name

Select InsertModule

Paste the macro in the window that opens on the right side of the screen.

Now, close the VBA editor and return back to your Excel file (click the
close "X")

Now you're ready to run the macro.

Goto the menu ToolsMacroMacros

Select the macro, FindMissing, click Run

Biff

"hana" wrote in message
...
I am not quite sure how to get the macro to work with an already created
set
of commands. Could you instruct me on how to do that. Then I will try it
to
see if it works.
--
Hana


"T. Valko" wrote:

Assuming that the range of numbers is in A1:A10 and that A1 = lower
boundary
and A10 = upper boundary:

Array entered:

=SMALL(IF(ISNA(MATCH(ROW($1:$25),A$1:A$10,0)),ROW( $1:$25)),ROW(A1))

This can be very slow on large sequences ~5,000+

Here's a macro by JMB:

Sub FindMissing()
Dim lngUpper As Long
Dim lngLower As Long
Dim i As Long
Dim rngData As Range
Dim lngcount As Long

Set rngData = Range("C1:C1000") 'change as needed
lngLower = 1 'start of sequence
lngUpper = 5000 'end of sequence
lngcount = 1

For i = lngLower To lngUpper
If Not IsNumeric(Application.Match(i, _
rngData, 0)) Then
Range("D" & lngcount).Value = i 'sets output to column D
lngcount = lngcount + 1
End If
Next i

End Sub

Biff

"Ron Coderre" wrote in message
...
Maybe something like this:

With your list of values in Cells A1:A10 (eg 1,2,5,6,10,11,15,20,21,25)

This ARRAY FORMULA lists the missing items in ascending order.
B1:
=INDEX(ROW($A$1:INDEX(A:A,MAX(A:A))),SMALL(IF(COUN TIF($A$1:INDEX(A:A,MAX(A:A)),ROW($A$1:INDEX(A:A,MA X(A:A)))+MIN(A:A)-1)=0,ROW($A$1:INDEX(A:A,MAX(A:A)))+MIN(A:A)-1),ROWS($1:1))-MIN(A:A))+MIN(A:A)

Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Once the list of missing numbers is complete,
Copy/PasteSpecial(value) to the bottom of the actual list

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"hana" wrote:

I have a list of numbers w/ data that doesn't include all of the
numbers.
I
need to add numbers to the list to complete the numerical list (so the
added
numbers would have no data in the row, just the number). It seems
like
such
a simple thing to do and yet I have no idea how to actually do it.
Does
anyone know something that would help me to complete this list?
--
Hana