Results 1 to 2 of 2
  1. #1
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    250
    Thanks
    3
    Thanked 1 Time in 1 Post

    Find Missing Numbers (VBA/Excel/2003)

    This seemed so easy when my customer asked for it. He has a bunch of spreadsheets that we need to get a list of missing numbers. The sheets always have the number in column A and are in numerical order. All I need is a list of numbers that are not in the list. Here is what I have tried:

    <pre>Option Explicit
    Option Base 1

    Sub FindMissing()
    Dim NumBooks As Long, LastCurr As Long, LastNum As Long, CurBook As String
    Dim A As Long, B As Long, NumRange() As Integer, C, D, E, F, G
    Close #1
    Open "MissingNumbs.txt" For Output As #1 'Output list
    For A = 1 To Workbooks.Count 'How many workbooks
    Workbooks(A).Activate
    If Workbooks(A).Name = ThisWorkbook.Name Then
    GoTo NextPlace 'Don't count this workbook
    End If
    Print #1, Workbooks(A).Name 'Add Name to list
    LastCurr = Application.CountA(ActiveSheet.Range("A:A"))
    LastNum = Cells(LastCurr, 1) 'Find last number to look for
    ReDim NumRange(LastCurr) 'Redefine the array
    For B = 1 To LastCurr
    NumRange([img]/forums/images/smilies/cool.gif[/img] = Cells(B, 1) 'Fill the array with all the numbers in list
    Next B
    E = 1
    '----------- This seemed like a good idea but is stupid ----------
    For C = LBound(NumRange) To UBound(NumRange) 'Begin reading array
    For D = E To LastNum 'Problem here!
    If C = D Then
    'nothing
    Else
    Print #1, C
    E = D + 1
    Exit For
    End If
    Next D
    Next C
    NextPlace: 'Skipped the macro book
    Next 'Next workbook
    Close #1 'Close the doc
    MsgBox "Done" 'Ta Da
    End Sub
    </pre>


    Perhaps someone has a better idea for me.
    Alan

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Find Missing Numbers (VBA/Excel/2003)

    Try this much shorter version. It has the added advantage of not activating the workbooks.

    Sub FindMissing()
    Dim LastRow As Long
    Dim wbk As Workbook
    Dim wsh As Worksheet
    Dim i As Long
    Dim j As Integer
    Dim f As Integer

    f = FreeFile
    Open "MissingNumbs.txt" For Output As #f 'Output list

    For Each wbk In Workbooks
    If wbk.Name <> ThisWorkbook.Name Then
    Print #f, wbk.Name 'Add Name to list
    Set wsh = wbk.ActiveSheet
    LastRow = wsh.Range("A65536").End(xlUp).Row
    For i = 2 To LastRow
    For j = wsh.Range("A" & (i - 1)) + 1 To wsh.Range("A" & i) - 1
    Print #f, j
    Next j
    Next i
    End If
    Next wbk 'Next workbook

    Close #f 'Close the doc
    MsgBox "Done" 'Ta Da
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •