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

 

[Bret1]

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

I sent reply below, but forgot to select the "Reply" button. Thank you very much for doing this!

 

[JLGWhiz]

The slow speed of the macro is probably caused by volatile formulas on your sheet. Turning off the calculation while the macro runs should improve the speed. But looping in a large database is usually a little slower than a sheet with just a couple hundred rows of data. I rearranged you mods and added the calculation statement. Give it a try.

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

I also had the Offset wrong for column V.

 

[Bret1]

Unfortunately, after further evaluation, this won't work for my application after all. But it is close!
Whenever, a cell down J17:J100000 <> V17:V100000, I actually need it to do the following to keep my "live" data in A:S lined up with my "text" data in V:KB...
This would be if J124 <> V124...
Select ALL of row 124, and insert row
Select A124:S124, and delete cells to shift Up
Select J124, and copy (as text) to V24 and KB24
Continue checking if J125 <> V125

.......J.................V...........KB
AA2AAAA.....2............2
AA4AAAA.....4............4
AA5AAAA.....6............6
AA6AAAA.....8............8
AA8AAAA.....10.........10
AA10AAA

 

[JLGWhiz]

I would not attempt to write code for that without being able to access the sheet to see where formulas reside, if any, and knowing how the data is created and entered into the worksheet. There are just too many variables to consider when rows are inserted and deleted which results in data and formulas being shifted. I am going to drop off this thread now. Perhaps someone else will junp in and offer code for what you want.