Exit for depending on the dynamic Range inputted in the worksheet

[JaimeMabini]

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.

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.

 

[Engberg]

So you want to stop at row 3421, even if there is more data? This should do that.

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

 

[JaimeMabini]

So you want to stop at row 3421, even if there is more data? This should do that.

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:

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