To Find and Insert column and add Formula and copy formula down

Hi,

After testing the code in the workbook you provided, I saw that the raw data header "Material description" had trailing spaces, why you can use the find function xlpart instead of xlwhole. In addition, the numberformatting of the raw data cells needs reformatting for formulas to work.

I did also tweak your code a little bit as I rewrote it:

Sub Insrt_MatType2()

    Dim rngFind As Range

    With Sheets("Raw Data")
        Set rngFind = .Rows(1).Find("Material description", , xlValues, xlPart)
        If Not rngFind Is Nothing Then
            rngFind(1, 2).EntireColumn.Insert
            rngFind(1, 2).Value = "Material type"
            With .Range(rngFind(2, 2), .Cells(Rows.Count, rngFind.Column).End(xlUp)(1, 2))
                .NumberFormat = 0
                .Formula = "=IF(ISNUMBER(FIND(""S"",A2)),""Sample"",IF(ISNUMBER(FIND(""Z"",A2)),""Sample"",IF(ISNUMBER(FIND(""T"",A2)),""Tester"",IF(ISNUMBER(FIND(""Y"",A2)),""Tester"",""FG""))))"
            End With
            Set rngFind = Nothing
        End If
    End With

End Sub

Hope it works.

Cheers,
berlan