Missing Numbers Range VBA

[Rhett]

Rick you also posted the following code on msdn:


Function MissingNumbers(Rng As Range) As String
Dim X As Long, MaxNum As Long
MaxNum = WorksheetFunction.Max(Rng)
ReDim Nums(1 To MaxNum)
For X = 1 To MaxNum
If Rng.Find(X, LookAt:=xlWhole) Is Nothing Then
MissingNumbers = MissingNumbers & ", " & X
End If
Next
MissingNumbers = Mid(MissingNumbers, 3)
End Function
-----

This function was very helpful. Can you add a couple of lines that builds a string that contains the addresses of the cells within the range, or is it not possible for a UFD?
Please advise.

Thanks

Rhett

[Rick Rothstein]

I am confused as to what you want. That code produces a comma separated list of the number that do not appear in the range of cells passed into it. I do not understand what addresses you want to see... the numbers are not there so there are no addresses for them. Can you clarify what you want please (providing a before and after example would be useful)?

[Rhett]

As I understand it, the MissingNumbers(A1:A15) function that you developed generates a string of missing numbers in the range where some of the cells are blank. I'm trying to use a similar function that contains a list of cells that could potentially hold any of those missing numbers. Using your range (A1:A15), it contained up to 15 numbers, the smallest was 7, the largest 22, with several blank cells. My range is (D4 to F6), which has 9 cells, some of the cells have a known number (1-9), no duplicates for example 4,7,8, 9. Most of the cells are empty, and can contain any of the missing numbers 1,2,3,4,6,8. I want to get the addresses of the blank cells in the range (D4 to F6). For example 1,2,3,4,6,8; what is the address for X. I realize that X cannot contain 4,7,8, 9.

X
8 9 4
7


Hope this clarifies question.

Thank you,

Rhett