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
Bookmarks