Need help to alter the missing numbers macro.

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,012
Hello,

The attached macro, which finds the missing, numbers from 1 to 30. I got it from under this link...


VBA Code:
Option Explicit
Sub MissingValues()

    Const MAX_NUMBERS = 30   ' 100000  ' Maxmimum value expected
    Const MAX_ROWS = 30      ' 3000    ' Number of rows in output list
     ' before using next column
    Dim blnNumbers(1 To MAX_NUMBERS) As Boolean
    Dim lngRow As Long
    Dim rngData As Range
    Dim lngIndex As Long
    Dim intCol As Integer
    
     ' mark all existing numbers as true
    For Each rngData In Range(Range("A1"), Range("A65536").End(xlUp))
        blnNumbers(rngData.Value) = True
    Next
    
     ' output list(s) of missing values
    intCol = 2
    For lngIndex = 1 To MAX_NUMBERS
        If Not blnNumbers(lngIndex) Then
            lngRow = lngRow + 1
            Cells(lngRow, intCol) = lngIndex
            If lngRow = MAX_ROWS Then
                lngRow = 0
                intCol = intCol + 1
                If intCol > 256 Then
                     MsgBox "No more space", vbExclamation
                    Exit Sub
                End If
            End If
        End If
    Next

End Sub

For example in the column "A" there are some random, macro find missing numbers in range 1 to 30 and list them in the column B As show in the example below with the sheet 1.

Example Sheet1

14
25
36
107
118
129
1315
1416
2217
2618
19
20
21
23
24
25
27
28
29
30

Please I need help to modify the missing numbers macro.

1-Which can find missing number from the column "A" and list them in to column "E"
2- can find missing number from the column "B" and list them in to column "F"
3- can find missing number from the column "C" and list them in to column "G"

As show in the example below with the sheet 2.

Example Sheet2

12227446
22229567
32729679
101147815
11548916
12251291517
132222151618
141924161720
22519171821
30148182023
104192126
67202328
252124
142326
292428
2529
26
27
28
29


Thank you all.

I am using Excel 2000

Regards,
Moti
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,383
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub motilulla()
   Const Mx As Long = 30
   Dim Cl As Range
   Dim i As Long
   
   With CreateObject("scripting.dictionary")
      For i = 1 To Mx
         .Add i, Nothing
      Next i
      For i = 1 To 3
         For Each Cl In Range(Cells(1, i), Cells(Rows.Count, i).End(xlUp))
            If .exists(Cl.Value) Then .Remove Cl.Value
         Next Cl
         Cells(1, i + 4).Resize(.Count).Value = Application.Transpose(.keys)
      Next i
   End With
End Sub
 
Solution

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,240
Office Version
  1. 2010
Platform
  1. Windows
Hi, a VBA demonstration as a beginner starter :​
VBA Code:
Sub Demo1()
        Dim C%, M, W, X, R&, V(1 To 30, 1 To 3)
    For C = 1 To 3
            M = [COLUMN(A:AD)]
        For Each W In [A1].CurrentRegion.Columns(C).Value2
            If W > 0 Then
                X = Application.Match(W, M, 0):  If IsNumeric(X) Then M(X) = False
            Else
                Exit For
            End If
        Next
            M = Filter(M, False, False)
    For R = 0 To UBound(M)
        V(R + 1, C) = M(R)
    Next R, C
        [E1:G30].Value2 = V
End Sub
 
Last edited:

JEC

Active Member
Joined
Aug 21, 2021
Messages
304
Office Version
  1. 365
Platform
  1. Windows
This will do

VBA Code:
Sub jec()
 ar = Range("A1:C30")
  For j = 1 To 3
      For i = 1 To UBound(ar)
        If Not IsNumeric(Application.Match(i, Application.Index(ar, 0, j), 0)) Then c00 = c00 & "|" & i
      Next
     Cells(1, j + 4).Resize(UBound(Split(c00, "|"))) = Application.Transpose(Split(Mid(c00, 2), "|"))
    c00 = Empty
  Next
End Sub
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,240
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

A beginner starter variation :​
VBA Code:
Sub Demo2()
    Dim C%, R&, W, N&, V(1 To 30, 1 To 3)
    For C = 1 To UBound(V, 2)
        R = 0
        W = [A1].CurrentRegion.Columns(C).Value2
    For N = 1 To UBound(V)
        If IsError(Application.Match(N, W, 0)) Then R = R + 1: V(R, C) = N
    Next N, C
        Cells(C + 1).Resize(N - 1, C - 1).Value2 = V
End Sub
 
Last edited:

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,012
How about
VBA Code:
Sub motilulla()
   Const Mx As Long = 30
   Dim Cl As Range
   Dim i As Long
  
   With CreateObject("scripting.dictionary")
      For i = 1 To Mx
         .Add i, Nothing
      Next i
      For i = 1 To 3
         For Each Cl In Range(Cells(1, i), Cells(Rows.Count, i).End(xlUp))
            If .exists(Cl.Value) Then .Remove Cl.Value
         Next Cl
         Cells(1, i + 4).Resize(.Count).Value = Application.Transpose(.keys)
      Next i
   End With
End Sub
Fluff, thank you according to my query and layout your code worked fine including I noticed that in column "G" I had 2 extra numbers 6 and 7 but your macro give me the correct answer.

Thank your for your help

Good luck

Kind Regards,
Moti
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,383
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
 

Forum statistics

Threads
1,141,294
Messages
5,705,537
Members
421,399
Latest member
hjweiss00

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top