Search code examples
excelvba

How can I use vba to insert a row below each row that contains "2024" in a specified column, and in that new row enter "2025" in same column?

I am downloading sales history from ERP system and want to add a row for next year's forecasting.
ERP export image:

enter image description here

..as text:

Employee.Employee   Company Product Year    Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total

Employee.1  Customer A  Product A   2023                                1                   1
Employee.1  Customer A  Product A   2024                        2                           2
Employee.1  Customer A  Product B   2024                        4                           4
Employee.1  Customer A  Product C   2024                        5                           5
Employee.1  Customer A  Product D   2024                        3                           3
Employee.1  Customer A  Product E   2024                        3                           3
Employee.1  Customer A  Product F   2023                                39  22              61
Employee.1  Customer A  Product F   2024                        19                          19

I have no vba training,,,just know there's a better way than manually. Appreciate any help/advice. The below works for adding the blank row. Now how do I get "2025" entered into Col D of newly inserted Row?

Sub Macro1()
    Dim RngToCheck As Range
    Set RngToCheck = ThisWorkbook.Sheets("Invoiced Tons by Customer by Mo").Range("D3:D5000")
    Dim ValToFind As Long
    ValToFind = 2024

    Dim i As Long
    For i = RngToCheck.Rows.Count To 1 Step -1
        If RngToCheck(i).Value = ValToFind Then
            RngToCheck(i + 1).EntireRow.Insert
        End If
    Next i
End Sub
Solution

  • Try this out:

    Sub Macro1()
    Dim ws As Worksheet, RngToCheck As Range, ValToFind As Long, i As Long
    
    Set ws = ActiveWorkbook.Worksheets("Invoiced Tons by Customer by Mo")
    'only check the occupied range
    Set RngToCheck = ws.Range("D3:D" & ws.Cells(Rows.Count, "D").End(xlUp).Row)
    ValToFind = 2024  'consider using Const instead of a variable here...

    Application.ScreenUpdating = False     'faster if you do this
    For i = RngToCheck.Cells.Count To 1 Step -1
        With RngToCheck.Cells(i)
            If .Value = ValToFind Then
                .offset(1).EntireRow.Insert 'add a row below the found value
                .offset(1).Value = 2025     'insert value on added row
            End If
        End With
    Next i
End Sub