Vba Insert Columns Based on Cell Value

[Ben AFF]

Hi, Im working on inserting columns based on cell value on the header row.
My bit of code (below) it is supposed to iterate over all columns header row and every instance it finds "YEAR" insert a column.
However it does not work as I intended and it inserts a big number of columns before the first occurrence of "YEAR" and then stops.

Can someone please explain why? Im more interested in understanding why this not work as I intended. Much thanks in advance.

Sub ColumnInsert()

  Dim k As Integer

  LC = Cells(1, Columns.Count).End(xlToLeft).Column
  For k = 2 To LC
    If Cells(1, k).Value = "Year" Then
    Cells(1, k).EntireColumn.Insert
    End If
  Next k

End Sub

 

[kevin9999]

When inserting (or deleting) columns/rows when you have an end point, you need to start from that end and work your way to the start, like this:

Option Explicit
Sub ColumnInsert()
    Dim LC As Long, k As Long
    
    LC = Cells(1, Columns.Count).End(xlToLeft).Column
    For k = LC To 2 Step -1
        If Cells(1, k).Value = "Year" Then Cells(1, k).EntireColumn.Insert
    Next k
End Sub

 

[Ben AFF]

Thanks Kevin, I never thought of that, much much appreciated.

 

[kevin9999]

You're welcome Ben