maxhayden
03-26-2009, 04:42 AM
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?? :think:
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
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?? :think:
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