Macro Fill Down Error

[spyldbrat]

My spreadsheet has a header row on row 4. The macro below is suppose to put a formula on row 5 in columns F and K and fill down to the last row of data. The macro is doing this, but after it adds the formula in F on row 5, it then copies down the HEADER row on row 4, wiping out the formulas. It is doing the same exact thing for column K. Why is this happening?

Sheets("NJN Lic Exp").Select
Dim UsdRws As Long
UsdRws = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("F5").Select
ActiveCell.Formula2R1C1 = _
"=MID(CELL(""filename"",R[-4]C[-5]),FIND(""]"",CELL(""filename"",R[-4]C[-5]))+1,255)"
Range("f5:f" & UsdRws).FillDown
Range("K5").Select
ActiveCell.FormulaR1C1 = _
"=CONCAT(RC[-3],"": "",R4C9,"":"","" "",RC[-2],"","",R4C10,"":"","" "",RC[-1])"
Range("k5:k" & UsdRws).FillDown

 

[Michael M]

Try it this way

Sub MM1()
Dim UsdRws As Long
UsdRws = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("F5:F" & UsdRws).Formula = "=MID(CELL(""filename"",A1),FIND(""]"",CELL(""filename"",A1))+1,255)"
Range("K5:K" & UsdRws).Formula = "=CONCAT(H5,"": "",$I$4,"":"","" "",I5,"","",$J$4,"":"","" "",J5)"
End Sub

 

[spyldbrat]

Dim UsdRws As Long UsdRws = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row Range("F5:F" & UsdRws).Formula = "=MID(CELL(""filename"",A1),FIND(""]"",CELL(""filename"",A1))+1,255)" Range("K5:K" & UsdRws).Formula = "=CONCAT(H5,"": "",$I$4,"":"","" "",I5,"","",$J$4,"":"","" "",J5)"

It works PERFECTLY!!!!! Thank you!

 

[Michael M]

Glad to help. ..?