(Excel VBA) How to insert multi columns and cell.v

Question

1. Background & purpose
I have a Excel sheet with table of multi-columns. I want to insert some columns(in yellow) via macro:

• "Total Cost" before "PIC" with formula: Total Cost = Man-hour * C (where C is constant)
• "Reviewed.Date", "Reviewer", "Year" before "Month" and formula to determine fiscal year ("Year") based on "Inspected.Date".
  Ex: Cell of Year = YEAR("Inspected.Date")+IF(MONTH("Inspected.Date")>=$D$1,1,0)

Please refer to this picture for more detail.

2. Problem & questions
I can loop through all columns and check if Column.header = "PIC" or "Month", then insert new columns. But I don't know how to insert "Column header" and "value of all cells in that column" with formula because after inserting new column, position of some columns will be changed. So, how can I do this with macro? Any helps would be highly appreciated. Thank you so much for your attention.

3. Here is my current VBA

Sub InsertNewColumn()
   'Edit SPO Documents.
   'Consider worksheet"Sheet1"

   With Worksheets("Sheet1")

       '1. Find out last columns & last row in target sheet.
        LastCol = .Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, _
                  SearchDirection:=xlPrevious).Column
        LastRow = .Cells.Find(What:="*", SearchOrder:=xlByRows, _
                  SearchDirection:=xlPrevious).Row        
       '2.Loop through all columns and check column header. 
       '  If column header is "PIC" or "Month", then insert new column

        For i = LastCol To 1 Step -1            
            On Error Resume Next            
            CellValue = .Cells(1, i)   'Get Column header   

            'Insert column "Total Cost"
            If CellValue = "PIC" Then
                .Cells(1,i).EntireColumn.Insert Shift:=xlToLeft
                .Cells(1,i).Value2 = "Total Cost" 'Add column header            
            End If

            'Insert column "Year"
            If CellValue = "Month" Then
                .Cells(1,i).EntireColumn.Insert Shift:=xlToLeft
                .Cells(1,i).Value2 = "Year" 'Add column header

                'Add formula to year
                With Sheets("Sheet1").Columns(i)
                    For j = LastRow To 2 Step -1                       
                        'Sheets("Sheet1").Cells(j, i).Formula = "=YEAR("Inspected.Date")+IF(MONTH("Inspected.Date")>=$D$1,1,0)"
                        'I want to add formula to determine fiscal year for cell of Year based on "Inspected.Date"
                        'but I dont know how to do
                    Next j               
                End With
            End If  
        Next i   
    End With
    Application.ScreenUpdating = True   
End Sub

Answer 1

You don't need a For ... Next loop so much as a reliable method of locating and inserting columns if they do not already exist.

dim c1 as variant, c2 as variant
with worksheets("sheet1")
    c1 = application.match("pic", .rows(1), 0)
    c2 = application.match("total cost", .rows(1), 0)
    if iserror(c2) and not iserror(c1) then
        .cells(1, c1).entirecolumn.insert
        .cells(1, c1) = "total cost"
    end if

    c1 = application.match("month", .rows(1), 0)
    c2 = application.match("reviewed.date", .rows(1), 0)
    if iserror(c2) and not iserror(c1) then
        c2 = application.match("inspected.date", .rows(1), 0)
        .cells(1, c1).resize(1, 3).entirecolumn.insert
        .cells(1, c1).resize(1, 3) = array("reviewed.date", "reviewer", "year")
        .range(.cells(2, c1+2), .cells(.rows.count, c2).end(xlup).offset(0, 4)).formula = _
            "=year(" & .cells(2, c2).address(0, 0) & ")+(month(" & .cells(2, c2).address(0, 0) & ")>=$d$1)"
    end if
end with

Note that I've modified your formula to the simpler form.

= YEAR("Inspected.Date")+(MONTH("Inspected.Date")>=$D$1)