How to modify a macro to replace a row with another instead of deleting the row.

[Talnick]

Hello everyone I am a student taking a class and right now we are learning excel. I have a very specific question I need help with. I have a sheet that has information on it and a code that will delete a row if the value is off by a certain percent inputted via a input box. I need to modify the code to replace the row with the one above it instead of deleting it. The pics show what is needed. I am on step 13 out of 17. Any assistance would be appreciated

 

[johnnyL]

The code included in your pictures = :

Sub TestCode()
'
    AllowableError = Val(Application.InputBox("What percentage can we be off by?"))         ' val converts a string to a number
'
    For Row = 2 To 10000                                                                    ' 'Row' is a reserved word and should not be used as a variable ;)
        ActiveSheet.Cells(Row, 2).Select                                                    ' Don't forget comments
        DataToCheck = Selection
'
        If Selection = Blank Then
            Exit For
        End If
'
        ActiveSheet.Cells(Row - 1, 2).Select
        GoodData = Selection
'
        PercentError = (DataToCheck - GoodData) / GoodData * 100
'
        If Abs(PercentError) > AllowableError Then
            Rows(Row & ":" & Row).Select
            Selection.Delete Shift:=xlUp
            Row = Row - 1
        End If
    Next
End Sub

The following code would be that code shortened up a bit, without all of those potentially code slowing 'select's as well as step #13 included:

Sub TestCodeShortenedAndStep13()
'
    AllowableError = Val(Application.InputBox("What percentage can we be off by?"))         ' val converts a string to a number
'   This should be part of additional added code to check if user backed out
''    If AllowableError = 0 Then MsgBox "User Quit"                                           ' Red 'X' was clicked, or Cancel was clicked
'
    For RowsToCheck = 2 To 10000                                                                ' Loop to go through rows of data
        DataToCheck = ActiveSheet.Cells(RowsToCheck, 2)                                         '   B column ... B2,B3,B4,etc
'
        If DataToCheck = Blank Then Exit For                                                    '   If cell is blank then Exit this For loop
'
        GoodData = ActiveSheet.Cells(RowsToCheck - 1, 2)                                        '   Set GoodData = previous cell in column
'
        PercentError = (DataToCheck - GoodData) / GoodData * 100                                '   Calculate PercentError
'
        If Abs(PercentError) > AllowableError Then Rows(RowsToCheck - 1).Copy Rows(RowsToCheck) '   replace row of bad data with GoodData, ie. copy above row to row that is below
    Next
End Sub

Step #13 to copy the above line to the line below it, can be represented with one line of code.

 

[Talnick]

The code included in your pictures = :

Sub TestCode()
'
    AllowableError = Val(Application.InputBox("What percentage can we be off by?"))         ' val converts a string to a number
'
    For Row = 2 To 10000                                                                    ' 'Row' is a reserved word and should not be used as a variable ;)
        ActiveSheet.Cells(Row, 2).Select                                                    ' Don't forget comments
        DataToCheck = Selection
'
        If Selection = Blank Then
            Exit For
        End If
'
        ActiveSheet.Cells(Row - 1, 2).Select
        GoodData = Selection
'
        PercentError = (DataToCheck - GoodData) / GoodData * 100
'
        If Abs(PercentError) > AllowableError Then
            Rows(Row & ":" & Row).Select
            Selection.Delete Shift:=xlUp
            Row = Row - 1
        End If
    Next
End Sub

The following code would be that code shortened up a bit, without all of those potentially code slowing 'select's as well as step #13 included:

Sub TestCodeShortenedAndStep13()
'
    AllowableError = Val(Application.InputBox("What percentage can we be off by?"))         ' val converts a string to a number
'   This should be part of additional added code to check if user backed out
''    If AllowableError = 0 Then MsgBox "User Quit"                                           ' Red 'X' was clicked, or Cancel was clicked
'
    For RowsToCheck = 2 To 10000                                                                ' Loop to go through rows of data
        DataToCheck = ActiveSheet.Cells(RowsToCheck, 2)                                         '   B column ... B2,B3,B4,etc
'
        If DataToCheck = Blank Then Exit For                                                    '   If cell is blank then Exit this For loop
'
        GoodData = ActiveSheet.Cells(RowsToCheck - 1, 2)                                        '   Set GoodData = previous cell in column
'
        PercentError = (DataToCheck - GoodData) / GoodData * 100                                '   Calculate PercentError
'
        If Abs(PercentError) > AllowableError Then Rows(RowsToCheck - 1).Copy Rows(RowsToCheck) '   replace row of bad data with GoodData, ie. copy above row to row that is below
    Next
End Sub

Step #13 to copy the above line to the line below it, can be represented with one line of code.

Ok that seems to work pretty well. But what about the last few steps? like 15 and 16? I need to highlight the rows to be yellow for the ones that got changed and then also have everything in coulum B to be sorted from smallest to largest. Again thank you so much for helping out.

 

[johnnyL]

We will assist you when needed, but we generally don't do homework assignments for people. Show us what you have tried and we will assist you,