PDA

View Full Version : Solved: Search Range of numbers and return missing



justdream
09-09-2011, 07:04 AM
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
09-09-2011, 08:54 AM
Return to you how?

justdream
09-09-2011, 08:58 AM
Sorry, I mean show me the missing numbers in Different column called Output for example in different cells

Kenneth Hobs
09-09-2011, 10:07 AM
Private Sub CommandButton1_Click()
'Early Binding method requires Reference: MicroSoft Scripting Runtime, scrrun.dll
'Dictionary Help File: http://www.microsoft.com/download/en/details.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

justdream
09-09-2011, 10:30 AM
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
09-09-2011, 10:44 AM
You have to set the reference in Tools > References as the first comment explained. Comments are your friend.