VBA Formula with Type mis-match error

I am working on a workbook that takes hyperlink address and through a series of macros with the final result is the file name only and hyperlinked with the original address.

In the VBAProject Module 4 the InsertFormulasStep2 macro is designed to insert formulas in cells based on the number of records in Sheet1.

To test the macros, start with the DeleteBlankRowsStep1 procedure.

The .Range formula is commented and is exactly the same as the formula in B1 of the worksheet (to verify the formula works correctly.)

I have also tried to write (unsuccessfully) IF statements for the formulas in columns D, E & F so if there is no text in the corresponding row in Column A, that the cells display blank.

I appreciate your help