generate missing numbers



I  try   finding  way  to  generate  the  missing  numbers    . the  numbers  are  existed in COL B and  the  missing  number  should  show  in COL I   .  missing  number  should start from 1 .  I  put  some result  in COL I  should be  1801,1802 .

this  is  what  I have  but  it  gives error type mismatch  

ReDim arrMiss(Range("B" & LR) - Range("B1") - LR)

Sub generatemissingnumbers()
Dim arrAll As Variant
Dim arrMiss As Variant
Dim LR As Long
Dim i As Long
Dim j As Long
Dim k As Long
Dim l As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
ReDim arrMiss(Range("B" & LR) - Range("B1") - LR)
    arrAll = Range("B1:B" & LR)
    For i = 1 To LR - 1
    l = 0
        For j = arrAll(i, 1) + 2 To arrAll(i + 1, 1)
        l = l + 1
        arrMiss(k) = arrAll(i, 1) + l
        k = k + 1
        Next j
    Next i
Range("I1").Resize(UBound(arrMiss) + 1) = Application.Transpose(arrMiss)
End Sub



I'm really not clear what you're trying to do- please revise your question to explain better what you are trying to do. Please cover the following points too...

There are some odd things in your file- the last row in B (LR) is 3712 currently (since B3712 contains the formula =SUM(B3697:B3710) ). I suspect you need to remove that.

Also do you have some local number formatting in column B? The numbers in column B look like numbers but Debug.Print Range("B2") gives me ?1803? rather than 1803. Feels like that will affect any mathematical or numerical operations you attempt. If so, what's your thinking there?
On the Redim statement (which make little sense), what size are you trying to set the array to?
John_Ru (rep: 1917) Jul 29, '21 at 10:40 am
about   the  formula   I  forgot   that   . now  i  delete  it  ,about  the  number  format  is  normal  normal  and   i  made  it  by  GENERAL ,but  it  shows  1,2,3   to  the last row in B (LR) is 3712  .it  should  show   until  filled lastrow ( 79) like this 1803  .   and  about Redim  I  expect increasing  data are  about 3500 rows
speed (rep: 24) Jul 29, '21 at 12:31 pm
Speed, good that you removed the formula in B712 but I'm still not clear what you are trying to do (or what the variable k is for). 

If all you want to do is produce over 3,000 numbers beginining with 1801 say, there's no need to use VBA, just see Don's tutorial Quickly Create a Huge List of Numbers in Excel

As I said, you need to EDIT your original question to explain what you want (since other contriburutors and users might not and should not need to look in the Discussion for details).
John_Ru (rep: 1917) Jul 29, '21 at 12:48 pm
Add to Discussion


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
                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
            ' don't use syntax for ranges when addressing cells
            .Cells(1, "I").Resize(i + 1) = Application.Transpose(Missing)
        End With
    End If
End Sub


wow !   this  is  very  impressive. this  is  exctly  what  I want .
thanks  so  much .
speed (rep: 24) Jul 30, '21 at 1:31 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login