Run this Macro if columns don't match

Bret1

Board Regular
Joined
Jun 14, 2013
Messages
129
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!

Code:
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
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
VBA Code:
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

Board Regular
Joined
Jun 14, 2013
Messages
129
VBA Code:
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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

Board Regular
Joined
Jun 14, 2013
Messages
129

ADVERTISEMENT

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

Board Regular
Joined
Jun 14, 2013
Messages
129
Since the last row is dynamic, it could just compare the numbers from J17:J100000 to V17:V100000.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Jun 14, 2013
Messages
129
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
See if this will do what you want.
VBA Code:
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

Board Regular
Joined
Jun 14, 2013
Messages
129
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!!

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,232
Messages
5,594,956
Members
413,954
Latest member
mrsandy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top