Hi All
I need to find the missing variable number or numbers within a range starting from 1 to xx. Then display the missing numbers in a msgbox or something similar (I’d prefer it was not in a cell).
So I have a range starting on B8 with number 1, then there could be up to 100 numbers on row 8, they will all be unique and sorted.
IE
B8 = 1
C8 = 2
D8 = 4
E8 = 6
F8 = 7
And so on
I I’ve set the max number and the range and I have a loop (I tend to use the codename of the worksheet as users have a habit of changing the Tab name). What I cannot work out is how to find and display all the missing number/s. IE 3 and 5 are missing.
Any help would be appreciated.
Thanks
Simon
I need to find the missing variable number or numbers within a range starting from 1 to xx. Then display the missing numbers in a msgbox or something similar (I’d prefer it was not in a cell).
So I have a range starting on B8 with number 1, then there could be up to 100 numbers on row 8, they will all be unique and sorted.
IE
B8 = 1
C8 = 2
D8 = 4
E8 = 6
F8 = 7
And so on
I I’ve set the max number and the range and I have a loop (I tend to use the codename of the worksheet as users have a habit of changing the Tab name). What I cannot work out is how to find and display all the missing number/s. IE 3 and 5 are missing.
Any help would be appreciated.
Thanks
Simon
Code:
Sub test()
Dim rng As Range
Dim i, lLastCol, lLastBox As Long
lLastCol = Sheets(Sheet12.Name).Range("8:8").Find("*", searchorder:=xlByColumns, searchdirection:=xlPrevious).Column
Set rng = Application.Sheets(Sheet12.Name).Range(Cells(8, "B"), Sheets(Sheet12.Name).Cells(8, lLastCol))
lLastBox = Application.WorksheetFunction.Max(Sheets(Sheet12.Name).Range("8:8"))
For i = 1 To rng.Rows.Count
Next
End Sub