Macro to Identify Number Grossly out of Sequence

[mstuf]

Hello -- I recently was helped to construct a macro to Compare and Prepare to Correct two columns of numbers with some cells that didnt match. The Macro adds blank cells to the column adjacent to the mismatched cell and proceeds on to the end of the columns. It works great ( Thank You ) when the columns are off just because of a missing number from sequence. BUT, Occasionally the program freezes Excel and the Macro Never completes. Analyzing the problem, I find that its where Occasionally my imported column of numbers will have a Number WAY WAY out of Sequence. This Causes it to attempt to add too many cells to the opposite column and runs excel out of rows needed to complete its task.

Rather than mess with the Previous Macro ( which I understand and can modify to my needs ) , I think another Macro to Search for Errant Numbers would be the best solution to run on my imported column before running the other macro.

I need a Macro that would run from the active cell on the column that cell is in. I need the Macro to stop where any number is NOT smaller than the number in the cell below it. That way I can correct the gross errors and restart the macro until I reach the end of the column. While the Error Numbers I search for could be a Grossly Larger or Smaller Number than belongs in the Group, the place it stops at will always be within a cell of the error I need to correct.


Then when I run the first above mentioned macro, it can perform as designed.

As a Novice -- it looks like a simple if then statement but my problem comes when I try to add a loop and declare variables to keep it going.

Thank You for Looking -- any ideas appreciated.

 

[cb12]

Try something along the line of this

Sub Sequencer()
Dim i As Integer
    For i = 1 To Selection.CurrentRegion.Rows.Count - 1
        If ActiveCell.Offset(-1, 0).Value = Empty Then
            ActiveCell.Offset(1, 0).Select
        End If
    
        If ActiveCell.Value < ActiveCell.Offset(-1, 0).Value Then
            ActiveCell.Offset(1, 0).Select
        End If
    Next i
End Sub

This won't work if the activecell is in the first row otherwise it will scroll through the column until it finds a cell that has a larger number than its predecessor. Perhaps try attaching a shortcut key to it to run through the whole data, alternatively if you will do a constant action on those out of sequence cells then it can be incorporated into the code so as it can run through the whole column in one go. The only other thing to bear in mind is it relies on there being no blank cells in the column if there are then the code will have to edited to accommodate that.

HTH

 

[mstuf]

Hello -- Thanks for the offer of Help -- But I think my detailed explanation may have been confusing. Here is an Example of the problem I am trying to identify and eliminate.

1 080001
2 080002
3 080003
4 080007
5 080008
6 080010
7 080012
8 046358
9 080015
10 080016
11 080017
12 126447
13 080019
14 080020

In my Example above -- Row 8 is NOT greater than row 7 so stopping there for correction. ( inserting 80013 or 80014 manually depending on which is correct based on other column info )
Then Restarting the Macro from Row 8 after correction then would find that Row 13 is NOT greater than row 12 - again stopping there for correction before restarting from row 12.

There will be NO empty cells.

The Columns I want to apply this to are 30000 - 40000 rows. The Errors as shown in example are usually only One sometimes maybe a Few Cells - Sometimes None, so looking for them by visual scan is VERY Cumbersome.

Your attempt was not wasted though -- I did learn several thing in trying to dissect and understand its operation. Thank You -- I continue to learn so much here. Dim Statements continue to outwit me but I keep working at it. I love to read threads here as time allows in my attempt to learn.

I Hope that my above example will help clarify what I need to do.

Thanks Again