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

[Akatecho]

Dear Expert Excel person,

I need you help me to find some solution, I try to write a macro as below :

Sub Insrt_MatType()
Dim Found As Range
Dim LR As Long
Set Found = Rows(1).Find(what:="Material description", LookIn:=xlValues, lookat:=xlWhole)
If Found Is Nothing Then Exit Sub
LR = Cells(Rows.Count, Found.Column).End(xlUp).Row
Found.Offset(, 1).EntireColumn.Insert
Cells(1, Found.Column + 1).Value = "Material type"
Range(Cells(2, Found.Column + 1), Cells(LR, Found.Column + 1)).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 Sub

I need to insert column and add formula in the new column and copy formula down, but I cannot add formula in this macro.

i do not how can solve this one, i need you advice me this case.

And I have attached Excel file, can you see in Tab sheet Raw data.


Best regards,

[berlan]

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:

Please Login or Register  to view this content.

Hope it works.

Cheers,
berlan

[Akatecho]

Thank you, this one can help me