finding all missing check numbers

[Jim]

Is there an easy macro to get a list of missing(outstanding) check numbers.
I can get a list of recent cleared check numbers and their amounts. It would
be nice to get a list of missing checks in a column so I could just enter
the missing checks amounts and have a sum of the total amount still
outstanding.

For example the banks list might have:
11188 with the amounts too
11186
11185
11183
11182
11181
11180

Is there a macro that would tell give me a list of 11187 & 11184 in a column
so I could list the corresponding amounts ans sum them? Thanks

 

[Guest]

One example. Select the list of check numbers that are cleared and this will
prompt for the lowest and highest check number and put the results in Column
D (change the destination range as necessary).

Sub Test()
Dim lngFirst As Long
Dim lngLast As Long
Dim i As Long
Dim lngCount As Long
Dim rngData As Range

Set rngData = Selection
lngFirst = CLng(InputBox("First Check Number"))
lngLast = CLng(InputBox("Last Check Number"))
lngCount = 0

For i = lngFirst To lngLast
If Not IsNumeric(Application.Match(i, _
rngData, 0)) Then
lngcount = lngcount + 1
Range("D" & lngcount).Value = i
End If
Next i

End Sub

 

[Jim]

Thanks for the quick response!!

One example. Select the list of check numbers that are cleared and this
will
prompt for the lowest and highest check number and put the results in
Column
D (change the destination range as necessary).

Sub Test()
Dim lngFirst As Long
Dim lngLast As Long
Dim i As Long
Dim lngCount As Long
Dim rngData As Range

Set rngData = Selection
lngFirst = CLng(InputBox("First Check Number"))
lngLast = CLng(InputBox("Last Check Number"))
lngCount = 0

For i = lngFirst To lngLast
If Not IsNumeric(Application.Match(i, _
rngData, 0)) Then
lngcount = lngcount + 1
Range("D" & lngcount).Value = i
End If
Next i

End Sub