Macro Fill Down Problem

[spyldbrat]

I am trying to run the macro below. Row 4 are my column headers. The formulas should go on Row 5 and then copy down to the last row of data. When the macro is run, the formula in row 5 is now replaced with the column header from row 4?

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

 

[MARK858]

Range("F5:F5" & UsdRws).Formula

Should be

Range("F5:F" & UsdRws).Formula

and

Range("K5:K5" & UsdRws).Formula

Should be

Range("K5:K" & UsdRws).Formula

You also don't need the Filldown lines if you are applying the formulas directly

Alter those first and test, then we'll see where we are

 

[spyldbrat]

I have multiple tabs in this spreadsheet that all have the header row starting on 4.. I made the changes you suggested but it seems that regardless of how many rows of data exist on each tab (each tab has a different number of rows), it is only copying down to # of rows that are on the first tab. Here is the macro for the first tabs within my spreadsheet

Sheets("NJN Lic Exp").Select
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)"


Sheets("NJN").Select
Range("K5:K" & UsdRws).Formula = "=CONCAT(H5,"": "",$I$4,"":"","" "",I5,"","",$J$4,"":"","" "",J5)"
Range("L5:L" & UsdRws).Formula = "=IFERROR(A5&F5&TEXT(G5,"" mm/dd/yy""),"" "")"


Sheets("NY Add Lic Info").Select
Range("J5:J" & UsdRws).Formula = "=CONCAT(G5,"": "",$H$4,"":"","" "",H5,"","",$I$4,"":"","" "",I5)"
Range("K5:K" & UsdRws).Formula = "=CONCAT(A5&F5)"


Sheets("NY Lic Exp").Select
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)"

 

[KenU]

Your variable UsdRws needs to be updated for each sheet.

Hope that helps.

Regards,
Ken

 

[spyldbrat]

It worked! Thank you!!!!