How to repeat macro for different Rows

Question

I have this code and it works for first 'On Livestock Change but wont for next? Each will have matching columns but separate rows. Any Help is appreciated!

'On Livestock Change
If Not Intersect(Target, Range("E19")) Is Nothing Then
Dim stockRow As Long
If Range("E19").Value <> Empty Then
If Range("B8").Value = Empty Then 'Not Existing livestock
If MsgBox("The customer is not currently in the Livestock list. Would you like to add it?", vbYesNo, "Customer Not Found") = vbNo Then Exit Sub
'Launch userform to add livestock

    Else 'Existing Customer Found  
        Livestockrow = Range("B8").Value ' Livestock Row  
        Range("L19:M19").Value = Livestock\_List.Range("C" & Livestockrow).Value 'DOB  
        Range("E20:I20").Value = Livestock\_List.Range("D" & Livestockrow).Value 'breed  
        Range("L20:M20").Value = Livestock\_List.Range("E" & Livestockrow).Value 'EID  
        Range("E21:F21").Value = Livestock\_List.Range("F" & Livestockrow).Value 'Registered  
        Range("I21").Value = Livestock\_List.Range("G" & Livestockrow).Value 'Registrration  
        Range("L21:M21").Value = Livestock\_List.Range("H" & Livestockrow).Value 'Registrration Number  
        Range("E22:I22").Value = Livestock\_List.Range("I" & Livestockrow).Value 'SIRE  
        Range("L22:M22").Value = Livestock\_List.Range("J" & Livestockrow).Value 'Sire Registrration Number  
        Range("E23:I23").Value = Livestock\_List.Range("K" & Livestockrow).Value 'Dam  
        Range("L23:M23").Value = Livestock\_List.Range("L" & Livestockrow).Value 'Dam Registrration  
          
    End If  
      
Else 'Is Empty  
    Range("L19:M19").ClearContents 'Clear DOB  
    Range("E20:I20").ClearContents 'Clear breed  
    Range("L20:M20").ClearContents 'Clear EID  
    Range("E21:F21").ClearContents 'ClearRegistered  
    Range("I21").ClearContents 'Clear Registrration  
    Range("L21:M21").ClearContents 'Clear Registrration Number  
    Range("E22:I22").ClearContents 'Clear'SIRE  
    Range("L22:M22").ClearContents 'Clear Sire Registrration Number  
    Range("E23:I23").ClearContents 'Clear Dam  
    Range("L23:M23").ClearContents 'Clear Dam Registrration  

End If  

End If

'On Livestock Change 2
If Not Intersect(Target, Range("E25")) Is Nothing Then
Dim stockRow As Long
If Range("E25").Value <> Empty Then
If Range("B18").Value = Empty Then 'Not Existing livestock
If MsgBox("The customer is not currently in the Livestock list. Would you like to add it?", vbYesNo, "Customer Not Found") = vbNo Then Exit Sub
'Launch userform to add livestock

    Else 'Existing Customer Found  
        Livestockrow = Range("B18").Value ' Livestock Row  
        Range("L25:M25").Value = Livestock\_List.Range("C" & Livestockrow).Value 'DOB  

    End If  
      
Else 'Is Empty  
    Range("L25:M25").ClearContents 'Clear DOB  

End If  

End If

End Sub

Answer 1

Hi Bobbie-P,

Welcome to the Microsoft community.

According to your code, it seems that you want the code to be applied to different rows whenever a change occurs in cells representing "Livestock Change." The current code handles two such changes (in cells E19 and E25), populating or clearing various ranges based on data in a "Livestock_List" and whether a match is found. However, the second part of your code seems incomplete as it only addresses the DOB field and references an inconsistent cell for determining the existence of livestock (B18 instead of a pattern like B8).

To create a more dynamic and reusable solution, I refactor the code into a subroutine that can be called for any row where a "Livestock Change" might occur. This will involve passing the target row number as a parameter to the subroutine and adjusting ranges dynamically based on that row.

Here's the refactored version of the code:

Private Sub HandleLivestockChange(ByVal targetRow As Long) 

    Dim livestockRow As Long 

    Dim baseRowOffset As Long ' Offset from the base row where data starts being populated/cleared 

    ' Assuming the first data population starts at row 19 with an offset of 0 for the first set of ranges 

    If targetRow = 19 Then 

        baseRowOffset = 0 

    ElseIf targetRow = 25 Then 

        baseRowOffset = 6 ' Offset for the second set of ranges 

    Else 

        MsgBox "Unhandled Livestock Change row: " & targetRow, vbExclamation, "Error" 

        Exit Sub 

    End If 

    If Not Intersect(Range("E" & targetRow), Target) Is Nothing Then 

        If Range("E" & targetRow).Value <> Empty Then 

            If Range("B" & targetRow - 11).Value = Empty Then ' Adjusted to maintain consistency 

                If MsgBox("The customer is not currently in the Livestock list. Would you like to add it?", vbYesNo, "Customer Not Found") = vbNo Then Exit Sub 

                'Launch userform to add livestock 

            Else 

                livestockRow = Range("B" & targetRow - 11).Value ' Adjusted offset 

                With Livestock_List 

                    ' Populate ranges dynamically based on baseRowOffset 

                    Range("L" & targetRow & ":M" & targetRow).Value = .Range("C" & livestockRow).Value 'DOB 

                    Range("E" & targetRow + 1 & ":I" & targetRow + 1).Value = .Range("D" & livestockRow).Value 'breed, and so on... 

                    ' Continue for other ranges, adjusting indices accordingly 

                End With 

            End If 

        Else 

            ' Clear contents dynamically 

            Range("L" & targetRow & ":M" & targetRow).ClearContents 'Clear DOB 

            ' Continue for other ranges to clear, adjusting indices accordingly 

        End If 

    End If 

End Sub 

Private Sub Worksheet_Change(ByVal Target As Range) 

    ' Call the subroutine for each monitored row 

    HandleLivestockChange 19 

    HandleLivestockChange 25 

End Sub 

In this refactoring:

• A new HandleLivestockChange subroutine is created to handle changes dynamically based on the target row.
• An If condition checks which row triggered the event and sets an offset accordingly.
• The subroutine now clears and populates ranges dynamically based on the baseRowOffset.
• The Worksheet_Change event calls this subroutine for each row where a "Livestock Change" could happen.

This approach makes it easier to extend the functionality to additional rows by simply calling HandleLivestockChange with the appropriate row number in the Worksheet_Change event handler. Ensure that all necessary ranges are updated within the subroutine according to the correct offsets for each row scenario.

Should you have further inquiries or need additional assistance about the code, please don't hesitate to respond.

Best Regards,

Jonathan Z - MSFT | Microsoft Community Support Specialist