Numbers which aren't

**mad mad max** · 05-02-2007, 04:20 AM

In many cells there are numbers which can be in range from 1 to 100 (I don't know how you call them in english, but these nubers are whole, natural numbers, not decimal). Some of the numbers can show up in more than one cell, some of them are only in one cell, and some of them maybe aren't in any cell. How can I get an information from Excel which of numbers in mentioned range are NOT in any cell?

**sweep** · 05-02-2007, 05:04 AM

Hi,

This code will tell you which numbers between 1 and 100 aren't present

Sub absentnumbers()

Dim loopx As Single
Dim cnum As Variant
Dim txtwarning As String
For loopx = 1 To 100
cnum = Application.WorksheetFunction.CountIf(Selection, loopx)
If cnum = 0 Then txtwarning = txtwarning & loopx & " Does not occur" & Chr(13)
Next
MsgBox (txtwarning)

End Sub

**daddylonglegs** · 05-02-2007, 05:23 AM

A formula approach.....

If your range of numbers is B1:C200 then to list all integers between 1 and 100 that don't appear anywhere in that range enter this formula in E1

=IF(SUM(IF(COUNTIF(B$1:C$200,ROW(INDIRECT("1:100")))=0,1))>ROW()-ROW(E$1),SMALL(IF(COUNTIF(B$1:C$200,ROW(INDIRECT("1:100")))=0,ROW(INDIRECT("1:100"))),ROW()-ROW(E$1)+1),"")

confirm with CTRL+SHIFT+ENTER and copy down column

Note: to confirm with CTRL+SHIFT+ENTER select cell with formula, press F2 then hold down CTRL and SHIFT keys and at the same time press ENTER. Curly braces like { and } will appear around the formula in the formula bar

**mad mad max** · 05-03-2007, 04:07 AM

About the first solution, where exactly I have to write this code? (This is why I put this problem in the category "new users" - because I am not an expert in Excel, or to be more precise, I am a beginner.) I tried second solution, but I get message on some error.

**sweep** · 05-03-2007, 04:50 AM

OK,

Press ALT+F11 (this opens the VBA environment)

Press F7 (this opens the worksheet code)

paste the code, then close the window

In excel, go to Tools > Macro > Run Macro (or ALT+F8) and select "absentnumbers", then press OK