Solved: Find the numbers out of sequence

[maxhayden]

Hi! I'm new at this so please don't laugh at my code.

I have written some VBA code that will find where numbers go out of sequence. Eg. 1,2,3,4,6,7,8,9 (5 is missing).

What it does is: highlight and copy the last row in sequence and the next row that starts the new sequence (i.e. 4 and 6). It does this by asking: If Cells(i + 1, 6) - Cells (i, 6) > 1 Then copy rows i and i + 1

What I am having trouble with now is this:

I don't want to show 4 and 6. I want to show 5. This is easy if there is only 1 number ever missing. I would just go: (Value of Cell i + 1) - 1.

BUT - what if you have a gap like this: 1,2,3,7,8,9?

How would I show 4,5,6??

Here is the code I have so far. It is currently set up to ignore errors because there are 1 or 2 cells which have text in them which I want to ignore but because they contain numbers and text in the string it still recognises them as "IsNumeric". I've also set it up to ingore blank cells.

Here you go:

Sub MissingOrderFinder()


'Continues regardless of error

On Error Resume Next

'This will sort the orders by required numbers, in ascending order

Range("A1:F10000").Sort Key1:=Range("F1"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal


startrow = 2
endrow = 10000


For i = startrow To endrow

If Cells(i, 6) <> "" Then

x = Cells(i + 1, 6) - Cells(i, 6)

If x > 1 Then

Rows(i).Interior.ColorIndex = 35

Rows(i + 1).Interior.ColorIndex = 44

Rows(i).Copy

Sheets("Missing Numbers").Select

For j = 1 To 10000

z = ""

If ActiveSheet.Cells(j, 1) = z Then

ActiveSheet.Cells(j, 1).Select

ActiveSheet.Paste

Exit For

End If

Next j

Sheets("OE Orders Raw Data").Select

ActiveSheet.Rows(i + 1).Select

Selection.Copy

Sheets("Missing Numbers").Select

For k = 1 To 10000

w = ""

If ActiveSheet.Cells(k, 1) = w Then

ActiveSheet.Cells(k, 1).Select

ActiveSheet.Paste

Exit For

End If

Next k

End If

End If

Next i

End Sub


You can try it out by putting some numbers in sequence in Column F in a sheet called "OE Orders Raw Data" and having an empty second sheet called "Missing Numbers"

Any help would be greatly appreciated.

Thanks,

Max

[maxhayden]

I've just had a thought, but still not sure how to write the code for it.

Here we go:

For 1,2,3, , , ,7,8,9:

(i + 1, 6) - (i, 6) = x

x - 1 = y

y is the number of missing numbers in the sequence.

Then if I add 1 to i for each number cumulatively, I get the missing numbers.

How will I code that though? Hmmmmm.

[maxhayden]

I did it!!

This is for anyone else's benefit as there isn't really any benefit in me talking to myself.

He we go:

For i = startrow To endrow

If Cells(i, 6) <> "" Then

x = Cells(i + 1, 6) - Cells(i, 6)

If x > 1 Then

Rows(i).Copy

Sheets("Test").Select

For j = 1 To 15

z = ""

If ActiveSheet.Cells(j, 6) = z Then

ActiveSheet.Cells(j, 1).Select

ActiveSheet.Paste

y = x - 1

Exit For

End If

Next j

For k = j + 1 To j + y

ActiveSheet.Cells(k, 6) = ActiveSheet.Cells(k - 1, 6) + 1

Next k

End If

End If

Next i

For m = startrow To endrow

Sheets("Test").Select

If ActiveSheet.Cells(m, 1) <> "" Then

ActiveSheet.Rows(m).ClearContents

End If

Next m

End Sub


The last bit just clears the rows that aren't missing and leaves a nice little space between each section. Sweet!

[Danny]

Maxhayden,

Could you explain, or give an example of the layout of the sheet again?
thanks.