Change attached code to affect the current highlighted row(s) when run?

B

Bret1

Board Regular
Joined
Jun 14, 2013
Messages
199
Hi,
Can you please help me change the attached code to affect only the current highlighted row(s)?
In this example, everywhere that row "741" is displayed, should be changed to whatever row is currently highlighted when macro is run... (or to whatever row the "A" column is selected - whichever is easier.)
Thanks!

Code: 
Sub Macro1()
'
' Macro1 Macro
'
'
Rows("741:741").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A741:S741").Select
Selection.Delete Shift:=xlUp
Range("J741").Select
Selection.Copy
Range("V741").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.Panes(4).Activate
Range("KB741").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
 
Sort by date Sort by votes
You have a potential conflict of things going on here, so we need to know the order that these things should happen in.

You said:
1. Starting at Row 17, compare "J" to "V", if they match, go to next row. If they don't match, run the code below
2. If "J" is blank, compare "A" and "U" for that row. If their 1st 5 characters match, continue to next row. If "A" and "U" do not match, stop Macro at that point with pop-up stating " "A" and "U" don't match. Macro Stopped."

What if "J" and "V" do NOT match because "J" is blank (then it matches BOTH of the conditions your outlined)?
Then are we running the "code" outlined in step 1 above, or doing the check outlined in step 2 above?
 
Upvote 0

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Joe4 said:
You have a potential conflict of things going on here, so we need to know the order that these things should happen in.

You said:
1. Starting at Row 17, compare "J" to "V", if they match, go to next row. If they don't match, run the code below
2. If "J" is blank, compare "A" and "U" for that row. If their 1st 5 characters match, continue to next row. If "A" and "U" do not match, stop Macro at that point with pop-up stating " "A" and "U" don't match. Macro Stopped."

What if "J" and "V" do NOT match because "J" is blank (then it matches BOTH of the conditions your outlined)?
Then are we running the "code" outlined in step 1 above, or doing the check outlined in step 2 above?
Click to expand...

WOW! You are very good....
You are correct that "J" could be blank and "V" not blank making them not match. However, ANYTIME "J" is blank, that takes priority to compare "A" and "U" on that row. If "A" and "U" match, continue to next row, allowing "J" and "V" unchanged.
 
Upvote 0
OK, see if this ordering works for you:
VBA Code: 
Sub Macro1()

Dim lr As Long
Dim r As Long

Application.ScreenUpdating = True

'Find last row in column J with data
lr = Cells(Rows.Count, "J").End(xlUp).Row

'Loop through all rows starting in row 17
For r = 17 To lr
    If Cells(r, "J") = "" Then
'       Check to see if if the first 5 characters or columns A and U do not match
        If Left(Cells(r, "A"), 5) <> Left(Cells(r, "U"), 5) Then
'           What to do if columns A and U do not match
            MsgBox "Columns A and U do not match on row " & r, vbOKOnly, "MACRO STOPPED!!!"
            Exit Sub
        Else
'           Compare columns J and V
            If Cells(r, "J") <> Cells(r, "V") Then
'               Run code if they do not match
                Rows(r).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
                Range("A" & r & ":S" & r).Delete Shift:=xlUp
                Range("J" & r).Copy
                Range("V" & r).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
                Range("KB" & r).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
            End If
        End If
    End If
Next r

Application.ScreenUpdating = False

End Sub
 
Upvote 0
Joe4 said:
OK, see if this ordering works for you:
VBA Code: 
Sub Macro1()

Dim lr As Long
Dim r As Long

Application.ScreenUpdating = True

'Find last row in column J with data
lr = Cells(Rows.Count, "J").End(xlUp).Row

'Loop through all rows starting in row 17
For r = 17 To lr
    If Cells(r, "J") = "" Then
'       Check to see if if the first 5 characters or columns A and U do not match
        If Left(Cells(r, "A"), 5) <> Left(Cells(r, "U"), 5) Then
'           What to do if columns A and U do not match
            MsgBox "Columns A and U do not match on row " & r, vbOKOnly, "MACRO STOPPED!!!"
            Exit Sub
        Else
'           Compare columns J and V
            If Cells(r, "J") <> Cells(r, "V") Then
'               Run code if they do not match
                Rows(r).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
                Range("A" & r & ":S" & r).Delete Shift:=xlUp
                Range("J" & r).Copy
                Range("V" & r).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
                Range("KB" & r).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
            End If
        End If
    End If
Next r

Application.ScreenUpdating = False

End Sub
Click to expand...


The Macro stopped with "A" did not match "U" (at the correct row), but it did not perform the main task below when "J" did not match "V" prior to that point.
It should compare "J" and "V" for every row after 17 and run (similar) to this code if <>, then continue to next row.
IF "J" is blank, "J" does not have to match "V". Compare "A" and "U" on that row. If 1st 5 characters match on "A" and "U", continue to next row. If they do not.... Stop Macro.

Code: 
Sub Macro2()
'
' Macro2 Macro
'

'
    Rows("183:183").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A183:S183").Select
    Selection.Delete Shift:=xlUp
    Range("J183").Select
    Selection.Copy
    Range("V183").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.Panes(4).Activate
    Range("KB183").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.Panes(3).Activate
    Range("T183").Select
    Application.CutCopyMode = False
End Sub
 
Upvote 0
If a blank "J" is found and "A" and "U" match on that row, continue to next row resuming comparing "J" to "V" as before.
It will be very rare that "A" and "U" (1st 5 characters) don't match when "J" is blank. But when they don't match, I'll need to make manual changes at the point before continuing. If they do match, continue comparing "J" and "V" at next row until next blank "J", or the end of data.
 
Upvote 0
OK, we still do not have the order of events clarified. This does not require any coding knowledge. I simply just need you to tell me exactly how the order or precedence and checks should occur. Here is what I thought you wanted, based on your last post.
Rich (BB code): 
If column J is blank then
    If columns A and U are different then
        Return Message and exit sub
    Else
        If columns J and V are different
            Then run code block
If this is not the correct order of events, please update it, just like in the format I show above.
Note that once it hits a red line, it will stop there and not go any further for that row.
And the one that has the "Exit Sub" will stop the macro and not check any more rows.
 
Upvote 0
I think your code may be correct, but just backwards? Again, I don't know code, but...
The main task is comparing "J" to "V" and "correcting" it if they don't match, starting at row 17, and continuing to next row if they do match.
The secondary task occurs whenever "J" is (rarely) blank. If "J" is blank, "J" does not have to match "V". On that row, compare "A" and "U". If they match, continue to next row comparing "J" and "V". If they don't, stop Macro at that point with pop-up.
 
Upvote 0
Yes.... It appears to just be backwards....
The correct order should be...
Code: 
 If columns J and V are different
       Then run code block
            Else
If column J is blank then
        If columns A and U are different then
            Return Message and exit sub
 
Upvote 0
Bret1 said:
Yes.... It appears to just be backwards....
The correct order should be...
Code: 
 If columns J and V are different
       Then run code block
            Else
If column J is blank then
        If columns A and U are different then
            Return Message and exit sub
Click to expand...
OK, that won't quite do it without an additional condition, because the "If column J is blank then" condition will NEVER be hit, as-is, since you said column V will never be blank.
So if column J is blank, your first condition will be met (columns J and V are different).

So the first condition needs to be changed to save "If columns J and V are different AND column J is NOT blank".

I think this should do it:
VBA Code: 
Sub Macro1()

Dim lr As Long
Dim r As Long

Application.ScreenUpdating = True

'Find last row in column J with data
lr = Cells(Rows.Count, "J").End(xlUp).Row

'Loop through all rows starting in row 17
For r = 17 To lr
'   If columns J and V are different and column J is not blank
    If (Cells(r, "J") <> Cells(r, "V")) And (Cells(r, "J") <> "") Then
'       Run code if they do not match
        Rows(r).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("A" & r & ":S" & r).Delete Shift:=xlUp
        Range("J" & r).Copy
        Range("V" & r).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("KB" & r).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    Else
'       If column J is blank and columns A and U are different
        If (Cells(r, "J") = "") And (Left(Cells(r, "A"), 5) <> Left(Cells(r, "U"), 5)) Then
'           What to do if columns A and U do not match
            MsgBox "Columns A and U do not match on row " & r, vbOKOnly, "MACRO STOPPED!!!"
            Exit Sub
        End If
    End If
Next r

Application.ScreenUpdating = False

End Sub
 
Upvote 0
@Joe4
SUCCESS!!!!! Thank You SOOOOO much for all your help on this! This will save me a TON of time moving forward.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Need help with macro Dynamic Range??
Replies
6
Views
173
pennbowl
P
excelbytes
  • Solved
VBA Code To Copy Data Into Next Blank Column
Replies
5
Views
75
mumps
mumps
A
  • Question
Macro to update list to last row on another sheet copy source from multiple sheets
Replies
0
Views
308
Azamamir
A
willow1985
  • Solved
Find first blank cell in column A in a Table - Runtime error 91
Replies
4
Views
104
willow1985
willow1985
C
  • Question
Run-time error -2147319767 (80028029) ... SOMETIMES
Replies
1
Views
72
footoo
F

Forum statistics

Threads
1,214,929
Messages
6,122,314
Members
449,081
Latest member
tanurai

Share this page

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
Back
Top