Solved: Search Range of numbers and return missing

[justdream]

Dears,

Could you please help me (with excel function or macro) to search occupied room numbers in columns (B & D) and return to me the free numbers in the range of (512 to 536)

[Kenneth Hobs]

Return to you how?

[justdream]

Sorry, I mean show me the missing numbers in Different column called Output for example in different cells

[Kenneth Hobs]

[VBA]
Private Sub CommandButton1_Click()
'Early Binding method requires Reference: MicroSoft Scripting Runtime, scrrun.dll
'Dictionary Help File: http://www.microsoft.com/download/en...s.aspx?id=2764
'Dictionary Examples and Discussion:
'http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/A_3391-Using-the-Dictionary-Class-in-VBA.html

Dim rnRange As Range, i As Integer, cell As Range
'Dim d As Object 'Late Binding method
Dim d As Dictionary 'Early Binding method
Set d = New Dictionary 'Early Binding Method

Set rnRange = Union(Range("B2", Range("B" & Rows.Count).End(xlUp)), _
Range("D2", Range("D" & Rows.Count).End(xlUp)))
For i = 512 To 536
d.Add i, i
Next i

For Each cell In rnRange
If d.Exists(cell.Value) Then d.Remove (cell.Value)
Next cell

If Not d.Count = 0 Then Range("E2").Resize(d.Count).Value = WorksheetFunction.Transpose(d.Keys())

Set d = Nothing
End Sub[/VBA]

[justdream]

Dear Expert,
Thanks for your support but I couldn't understand your code, hereby I received this message error (Compile error: User-defined type not defined)

and Highlighting below script
d as Dictionary

Sorry, I'm confuded and don't know how to fix it out!

[Kenneth Hobs]

You have to set the reference in Tools > References as the first comment explained. Comments are your friend.