Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations cowski on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

Finding Missing Numbers 2

Status
Not open for further replies.

gbarritt

Civil/Environmental
Joined
Aug 2, 2001
Messages
3
Location
US
I need to develope a VB Macro for Excel 97 to find missing
numbers from a column of consecutive numbers. Does anyone have a macro that would help.
 
Missing numbers like empty cell
or like is there a 10 in this column
or which numbers from 1-100 are not in the column?
 
Name the range in which you're searching as 'MyRange'

Use the following code (written extempore - so u may have to fine-tune it)

Sub FindMissing()
Dim rng as Range
Set rng=Range("MyRange")
FirstNum=rng.SpecialCells(xlCellTypeConstants, xlNumbers)
NumRow=WorksheetFunction.Match(FirstNum, rng, 0)
Num1=FirstNum-NumRow+1
Num2=Num1+Rng.Rows.Count-1
Msg=""
For i=Num1 to Num2
K=0
On error resume next
K=WorksheetFunction.Match(i,rng, 0)
If K=0 then msg=msg+cstr(i)+","
Next
If msg="" then
msg="no missing numbers"
Else
msg=msg+" are the missing nos "
End if
MsgBox (msg)

End sub

AbsNumRow=rng.Rows(NumRow).Row
 
Mala,

Thank you very much for the Macro. I feel very stupid for submitting the request -- for an apparently very simple problem. I have been struggling with it for about three days now and could come up with a solution. I have a very sophisticated program for modeling water pipes for flow and pressure calculation for water agencies. When assigning junctions the system, it cannot determine the next available junction number available. One has to extract the junc. no's, export them to excel, sort, and go look for missing numbers. You have saved me a lot of time.

Thanks Guy
 
No genuine problem should be considered stupid!
I've worked on such problems as yours - so feel free to ask if you have any specific technical queries... though this forum is the best place for spreadsheet related matter.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top