Find missing numbers in a sequence

[Firesword]

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

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

 

[Fluff]

What version of Excel are you using?
Please update your account details to show this.

 

[Firesword]

I'm sorry I didn't include this, I'm using office 2016, Windows 10

 

[Fluff]

Thanks for that, how about

Sub Firesword()
   Dim lastcol As Long, i As Long
   Dim Ary As Variant
   Dim Tmp As String
   
   With Sheet12
      lastcol = .Cells(8, Columns.count).End(xlToLeft).Column
      Ary = Application.Index(.Range(.Cells(8, 2), .Cells(8, lastcol)).Value2, 1, 0)
      For i = .Cells(8, 2).Value To .Cells(8, lastcol).Value
         If IsError(Application.Match(i, Ary, 0)) Then Tmp = Tmp & vbLf & i
      Next i
   End With
   MsgBox "Missing numbers are" & Tmp
End Sub

 

[Firesword]

Perfect and very elegant.

Thank you

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Firesword]

Hi Fluff

I'm sorry to ask but I learn better when I understand something rather than just copying.
On your code your lines starting with ARY =, and For i =, you code has .cells .range is this part of the "with" ie it's putting sheet12 in front of the dot?

Also the line ARY this will store all the values in this variable (I didn't know you could do this), it there a limit of how many can be stored?

I also didn't know about isError either. I've made notes on all of them, so I can use them else where.

Again thank you for the help.

Simon

 

[Fluff]

On your code your lines starting with ARY =, and For i =, you code has .cells .range is this part of the "with" ie it's putting sheet12 in front of the dot?

That's right it saves typing the samething out & shortens the code. It can also speed the code up.

There are limits to how much data you can pull into an array from the worksheet, but the Application.Index is the limiting factor in the code & I think the limit is about 32,000 rows.

 

[Firesword]

Great thanks again for the quick reply.

 

[Fluff]

My pleasure.