+ Reply to Thread
Results 1 to 1 of 1

VBA code assistance.

  1. #1
    Registered User
    Join Date
    03-11-2005
    Posts
    1

    VBA code assistance.

    Hi there,

    I am very new to both VBA and forums - both as of yesterday.

    I have a column of numbers between 1 to 1 000 000. Not all the numbers are in the list. EG between 1 to 10 only 3,5 and 7 may be there. (the numbers have been used as unique ID's). I need to create a list that shows me all the numbers that have not been used. I have been given the below code - which I have adapted to my specific problem but it does not work. (run time error 13 - type mismatch). When I debug it the following appears :

    [code]Sub MissingValues()

    Const MAX_NUMBERS = 1000000 ' 100000 ' Maxmimum value expected
    Const MAX_ROWS = 10 ' 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("A56959").End(xlUp))
    blnNumbers(rngData.Value) = True (this is highlighted when i debug)
    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[code/]

    I would appreciate any help that i can get. I have know idea really.

    thanks guys and girls
    Last edited by badger55; 03-11-2005 at 09:35 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1