Finding Missing Numbers In A Sequence

Hi,

I am trying to find the missing numbers in a sequence from a list using a macro, my current code currently does this but only when I type the data directly into excel, when I import the data from a text file and run the Macro I get the "Sub script out of range" error.

My current code is:

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("A" & Rows.Count).End(xlUp).Row
ReDim arrMiss(Range("A" & LR) - Range("A1") - LR)
 
    arrAll = Range("A1:A" & 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
 Columns("B").ClearContents
Range("B1").Resize(UBound(arrMiss) + 1) = Application.Transpose(arrMiss)

Does anyone know a way around this? Any help would be appreciated

Im justing using a test file at the moment with the numbers:

101
102
103
105
106

I think this line will give you an error
ReDim arrMiss(Range("A" & LR) - Range("A1") - LR).
Try

Sub tst()
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("A" & Rows.Count).End(xlUp).Row
 arrAll = Range("A1:A" & LR)
ReDim arrMiss(Range("A" & LR))
'or this line ReDim arrMiss(1 To UBound(arrAll, 1))
 
    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
 Columns("B").ClearContents
Range("B1").Resize(UBound(arrMiss) + 1) = Application.Transpose(arrMiss)
End Sub

Hi,

Thanks for the reply!

Yes it is that line of code that is causing the error

I have tried both of the options above and although it doesn't throw an error anymore, now it doesn't tell me which numbers are missing

This line of code doesn't throw any errors or any missing numbers when run

ReDim arrMiss(Range("A" & LR))

This line of code doesn't throw any errors but displays an "#N/A" in the cell B7 when run

ReDim arrMiss(1 To UBound(arrAll, 1))

Because the redim starts at 1, not zero, k requires a value. Change the order of these lines

arrMiss(k) = arrAll(i, 1) + l
        k = k + 1

INTO
  k = k + 1
 arrMiss(k) = arrAll(i, 1) + l

Still get the same "#N/A"

I ran a step into on the line of code and it says "Run time error 13: Type Mismatch" although I cant see where the data is mismatched

You need to remove 1 if you redim it from 1

Range("B1").Resize(UBound(arrMiss)) = Application.Transpose(arrMiss)

Yeah that's what I did before, but I still get the same "#N/A" and "Run time error 13: Type Mismatch" error

Please post a sample, but this code works on the sample you posted

Sub tst()
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("A" & Rows.Count).End(xlUp).Row
 arrAll = Range("A1:A" & LR)
'ReDim arrMiss(Range("A" & LR))
ReDim arrMiss(1 To UBound(arrAll, 1))
 
    For i = 1 To LR - 1
    l = 0
        For j = arrAll(i, 1) + 2 To arrAll(i + 1, 1)
        l = l + 1
        k = k + 1
        arrMiss(k) = arrAll(i, 1) + l
        
        Next j
    Next i
 Columns("B").ClearContents
Range("B1").Resize(UBound(arrMiss)) = Application.Transpose(arrMiss)
End Sub

Hi

I have attached a copy of the excel file which includes my imported text data, the code works fine if I physically type the data into the spreadsheet, but not when I import it

Thanks for your help

I am sure what do you mean when you import it. I suspect the data which has imported may not be numbers, but this should not matter as long as the data are clean. How and from which system you are importing in to excel? It is most likely that the imported data may have unseen and non-printed characters. You need to use clean and trim functions to clean the data before you apply the code.

I am importing it using the Data>Get External Data>From Text File menu on Excel

I thought that, that maybe the format of the cell wasn't correct so when I imported it, it gives you the option to import that column as a number (which I used)

But I have attached a sample of the spreadsheet with the imported data and the text file im getting the data from

Could you import them in to excel and attach the sample so we can see what type of data you have? If you are importing text, how can you find missing numbers? Texts are strings not numbers. The data might appear numbers to your eyes, but not to excel. You somehow need to convert them in to numbers or you are not looking for missing number.

Kitty,
Please find attached.
Sheet1 is your sheet. I have created sheets 2 and 3.
Sheet 2 is imported with out removing 1004. In sheet-3 I have deleted 104 from the text so as to test for missing numbers. As you can see, the code works and gets 104 in column B.

This is how I have done it.

Import-text-delimited- Comma- then "Do not import the first 6 columns by highlighting each column and tick "Do not import box(Skip) then the last column choose "General" format. This should only import a single column which is the last column after the comma.

That works perfectly!

Thank you very much

Could you tell me where I was going wrong? I still don't fully understand what the problem was, the only thing different I did was the options when importing it

My guess is that you imported all the data, but you forgot to change the column you are looking for. For e.g., if you had imported the data as it is, the last column which has the number is the last column. Let's say you have 7 columns, so you change the array

 arrAll = Range("A1:G" & LR)

For j = arrAll(i, 7) + 2 To arrAll(i + 7, 1)
   arrMiss(k) = arrAll(i, 7) + l