Selected Answer
Hi Speed,
You formatted the numbers in column B as "Accounting" which adds some double-width characters to them which the code below removes to get at the real numbers. I think the code will also work if the formatting is changed. Another problem hails from a blank in row 79 (maybe more further down). My code just ignores it but that may not match your intention.
Anyway, please try this code and see what it does. I have added some comments which I hope will help you further improve your skills :-)
Option Explicit
' 1. Always use Option Explicit.
' 2. Always use caps & smalls in variable & procedure names.
' 3. Always type the names all-smalls in your code.
' = Together, these three will give you a visual check on typos:-
' If you typed correctly the capitalization will change.
' If it doesn't change there is a mistake.
Sub GenerateMissingNumbers()
' 297
' define your variables
Dim Ws As Worksheet ' the tab to work on
Dim Rng As Range ' relevant range in worksheet
Dim Numbers As Variant ' value of Rng
Dim Missing As Variant ' result array
Dim Number As Long ' last serial number
Dim i As Long ' index to Missing
Dim R As Long ' loop counter: Numbers index
Set Ws = Worksheets("Process") ' always define the worksheet
With Ws
' set the range: first cell to last cell:-
' (excluding the first row means Rng rows are smaller
' than sheet rows by Sheet.Row - Rng.Row)
Set Rng = .Range(.Cells(2, "B"), _
.Cells(.Rows.Count, "B").End(xlUp))
' Debug.Print Rng.Address(0, 0) ' check the definition
End With
' read your numbers and remove formating
Numbers = Rng.Value
For R = 1 To UBound(Numbers)
On Error Resume Next ' error if empty: set to 0
If (Asc(Numbers(R, 1)) < Asc("0")) Or _
(Asc(Numbers(R, 1)) > Asc("9")) Then
Numbers(R, 1) = Mid(Numbers(R, 1), 2)
End If
Numbers(R, 1) = Val(Numbers(R, 1))
Next R
On Error GoTo 0
Number = Numbers(1, 1) ' first number in Rng
' define the result array much larger than needed
ReDim Missing(10000)
For R = 2 To UBound(Numbers) ' start reading from 2nd number
If Numbers(R, 1) > Number Then ' ignore blanks or lower numbers
Do
Number = Number + 1
If Number < Numbers(R, 1) Then
i = i + 1
Missing(i) = Number
End If
Loop While Number < Numbers(R, 1)
End If
Next R
' i will be zero if no missing where found
If i Then
ReDim Preserve Missing(i) ' delete unused elements of Missing
Missing(0) = "Missing"
With Ws ' always qualify the ranges you write to
.Columns("I").ClearContents
' don't use syntax for ranges when addressing cells
.Cells(1, "I").Resize(i + 1) = Application.Transpose(Missing)
End With
End If
End Sub
