Run this Macro if columns don't match

[Bret1]

Hi,
I'm seeking Macro help that compares the number in J17 to the number in V17. If they match, compare next row numbers at J18 and V18 (stop when 5 blank rows in each cell are found). If they do not match, run the following Macro for THAT row... This Macro represents J341 not matching V341... Basically, it selects the whole row at 341 and inserts new row, selects A341:S341, and deletes that portion (up), then copies J341, and special pastes (as text) to V341 and KB341...
Thanks!

Sub Line_up_row()
'
' Line_up_row Macro
'

'
    Rows("341:341").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A341:S341").Select
    Selection.Delete Shift:=xlUp
    Range("J341").Select
    Selection.Copy
    Range("V341").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    'ActiveWindow.Panes(4).Activate
    Range("KB341").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
End Sub

 

[JLGWhiz]

stop when 5 blank rows in each cell are found

Is is an impossibility. Please edit your narrative so that it is not ambiguous.

 

[Bret1]

stop when 5 blank rows in each cell are found

Is is an impossibility. Please edit your narrative so that it is not ambiguous.

Sorry, I guess I did not explain it clear. I just meant for the macro to stop checking the next row (and end) when it finds empty cells in 5 consecutive rows in "J" and "V". This is just to give the Macro a stopping point since the "last" used row # is dynamic. But if there are 5 blank rows, there won't be any more used.

 

[JLGWhiz]

I don't think I will try to write code for this. There are too many conditions that could exist that would need to be checked before running the type of code you want to fix the problem. It can be done, but it would take more time than I want to put into it.

 

[Bret1]

I don't think I will try to write code for this. There are too many conditions that could exist that would need to be checked before running the type of code you want to fix the problem. It can be done, but it would take more time than I want to put into it.

OK.... But there was really no need for you to take the time to read my request, reply to it that you did not understand my request, then reply again that you will not be helping me with my request. Hopefully, someone else will be able to help me with it. Thanks though.

 

[Bret1]

Since the last row is dynamic, it could just compare the numbers from J17:J100000 to V17:V100000.

 

[JLGWhiz]

Let me explain a little further what you are up against if you try to adjust the columns based on a mismatch.
Scenario 1: Row 10 col J <> col V. Reason: col v is blank. Run code to shift col V up one row on cell V10. Result: col J and V still mismatch on row 10 and all rows beneath now mismatch.
Scenario 2: Row 10 col J <> col V. Reason: Typo error. Run code to shift col V up one row on cell V10. Result: col J and V still mismatch on row 10 and all rows beneath now mismatch.

Writing code to determine why there is a mismatch before arbitrarily shifting an entire column of data and wrecking the entire data base would be burdensome. I suggest you rethink your approach to try and avoid the mismatches resulting from user transactions rather than trying to fix the problem after the fact. If you are deleting cells in either column that you are trying to match and causing the cells in that column to shift up or down, then that is where you should be looking to put a fix.

 

[Bret1]

Let me explain a little further what you are up against if you try to adjust the columns based on a mismatch.
Scenario 1: Row 10 col J <> col V. Reason: col v is blank. Run code to shift col V up one row on cell V10. Result: col J and V still mismatch on row 10 and all rows beneath now mismatch.
Scenario 2: Row 10 col J <> col V. Reason: Typo error. Run code to shift col V up one row on cell V10. Result: col J and V still mismatch on row 10 and all rows beneath now mismatch.

Writing code to determine why there is a mismatch before arbitrarily shifting an entire column of data and wrecking the entire data base would be burdensome. I suggest you rethink your approach to try and avoid the mismatches resulting from user transactions rather than trying to fix the problem after the fact. If you are deleting cells in either column that you are trying to match and causing the cells in that column to shift up or down, then that is where you should be looking to put a fix.

None of that scenario comes into play with my request. If J17 <> V17 and the "Line_Up_Row" Macro I posted is run (for row 17) it WILL then correct cells J17 to match V17. I know this based on my knowledge of how the file I'm using works. Therefore, ALL I'm asking is for a Macro to compare cells J17:J100000 to V17:V100000. Starting at row 17, if any in J <> V for that row, the Macro I posted is run (for that row), then continue comparing the cells at the next row down. Each row for J and V will match after the macro completes.

 

[JLGWhiz]

See if this will do what you want.

Sub Line_up_row2()
Dim c As Range
With ActiveSheet
    For Each c In .Range("J17:J100000")
        If c <> "" Then
            If c.Value <> c.Offset(, 19).Value Then
                c.Offset(, 19) = c.Value
                .Range("KB" & c.Row) = c.Value
            End If
        End If
    Next
End With
End Sub

 

[Bret1]

WOW! That is SUPER IMPRESSIVE! It worked great!
Two things though....
1) It did not update "V" like "KB", so I just copied the "KB" line above it and changed "KB" to "V". It seems to worked, but I don't know if that was the "correct" way to do it.
2) It goes VERY slow to run the Macro. I tried adding "Application.ScreenUpdating = False" and "True" at beginning and end to maybe speed it up, but I don't know if I placed them at the correct points. It still takes a while to execute.... Below is how I edited it.... any ideas to help speed it up? Thanks very much!!

Sub Line_up_row2()
Dim c As Range
With ActiveSheet
Application.ScreenUpdating = False
    For Each c In .Range("J17:J100000")
        If c <> "" Then
            If c.Value <> c.Offset(, 19).Value Then
                c.Offset(, 19) = c.Value
                .Range("V" & c.Row) = c.Value
                .Range("KB" & c.Row) = c.Value
                Application.ScreenUpdating = True
            End If
        End If
    Next
End With
End Sub