Exit for depending on the dynamic Range inputted in the worksheet

JaimeMabini

New Member
Joined
Dec 29, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
Hello VBA Guru's!

I am fairly new to VBA. I have here a VBA code that compares attributes from sheet1 to sheet2. if attributes in sheet1 column A is found in sheet2 column A, copy the entire row in sheet1 and paste/replace that row in sheet2.

VBA Code:
Sub UpdateSheet2()
  Dim i As Long
  Dim f As Range, c As Range
  
  Application.ScreenUpdating = False
  With Sheets("Sheet2")
    For Each c In .Range("A1", .Range("A" & Rows.Count).End(3))
      Set f = Sheets("Sheet1").Range("A:A").Find(c.Value, , xlValues, xlWhole, , , False)
      If Not f Is Nothing Then
        f.EntireRow.Copy
        .Range("A" & c.Row).PasteSpecial xlValues
      End If
    Next
  End With
  Application.CutCopyMode = False
End Sub

What I need now in addition to this code. is an exit statement. I need the VBA job to end and exit automatically when it reaches row 3421 or it can dynamically change depending on the input of the end user in sheet1.

Thank you and any help will be highly appreciated.

Best Regards.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
So you want to stop at row 3421, even if there is more data? This should do that.

VBA Code:
Sub UpdateSheet2()
  Dim i As Long
  Dim f As Range, c As Range
 
  Application.ScreenUpdating = False
  With Sheets("Sheet2")
    For Each c In .Range("A1", .Range("A" & Rows.Count).End(xlUp))
        If c.row > 3421 Then
            MsgBox ("Row 3421 reached")
            End
        Else
            Set f = Sheets("Sheet1").Range("A:A").Find(c.Value, , xlValues, xlWhole, , , False)
            If Not f Is Nothing Then
                f.EntireRow.Copy
                .Range("A" & c.row).PasteSpecial xlValues
            End If
        End If
    Next
  End With
  Application.CutCopyMode = False
End Sub
 
Upvote 0
Solution
So you want to stop at row 3421, even if there is more data? This should do that.

VBA Code:
Sub UpdateSheet2()
  Dim i As Long
  Dim f As Range, c As Range
 
  Application.ScreenUpdating = False
  With Sheets("Sheet2")
    For Each c In .Range("A1", .Range("A" & Rows.Count).End(xlUp))
        If c.row > 3421 Then
            MsgBox ("Row 3421 reached")
            End
        Else
            Set f = Sheets("Sheet1").Range("A:A").Find(c.Value, , xlValues, xlWhole, , , False)
            If Not f Is Nothing Then
                f.EntireRow.Copy
                .Range("A" & c.row).PasteSpecial xlValues
            End If
        End If
    Next
  End With
  Application.CutCopyMode = False
End Sub

Hello Engberg,

This is working accordingly. Big Thanks!! ?

I made a few adjustment tho. I want the Range to be dynamic depending on the inputted row number by the end user. Also, I added a "Yes" or "No" to show if the row was copied from the other sheet. I added the code below for future cases:

VBA Code:
Sub UpdateSheet2()

'On Error Resume Next

  Dim i As Long
  Dim f As Range, c As Range
  Sheets("Sheet1").Select
  last = Range("B19").Value
 
  Application.ScreenUpdating = False
  With Sheets("Sheet2")
    For Each c In .Range("A1", .Range("A" & Rows.Count).End(xlUp))
    
        If c.Row > last Then
            MsgBox ("Row " & last & " Reached")
            End
            
        Else
            Set f = Sheets("Sheet1").Range("A:A").Find(c.Value, , xlValues, xlWhole, , , False)
            
            If Not f Is Nothing Then
                f.EntireRow.Copy
                .Range("A" & c.Row).PasteSpecial xlValues
                .Range("T" & c.Row).Value = "Yes"
                
            Else
                .Range("T" & c.Row).Value = "No"
                
            End If
            
        End If
        
Next

  End With
  Application.CutCopyMode = False
  
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

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