VBA formula help

clh14_96 

You clear the entire worksheet Load File.

So when you cut the (now empty) columns MM:N and insert a column into column A, the entire column A will be empty. That makes the formula pretty useless, if I interpret this correctly.

Apart from that, try this:

Below the line

LastRow = ...

Insert a new line

MsgBox LastRow

Run the macro. What is the value of LastRow?

clh14_96 

The worksheet name "Load File" might have been misleading to Hans. It appears to be a data staging area, so initially clearing it is appropriate.

Like Hans, I wonder what the value of your LastRow variable would be. I see two likely problems in the code:

1. The Formulas array (of Variants, as you did not specify a data type) does not specify a lower bound. That's typical coding, but then in the absence of an explicit lower bound*, someone examining the code has to check for an Option Base statement at the top of the code module. If the array's lower bound is 0, your assignment to the Formula property will store the zeroth entry. But without a prior assignment to the zeroth entry, it contains Empty (the default value for uninitialized Variants).
2. In my testing of the FillDown method, I noticed that if the range is a single cell, the formula that was stored there is erased. With your code, if LastRow is 8, the range is C8:C8, a single cell.

* An explicit lower bound would be writing a declaration such as "Dim Formulas(0 To 1)" or "Dim YearsOfData(1950 To 2022)", with or without the As clause.

So consider using this code in place of your With block:

    '----   Insert a column after those two.  From row 8 thru the last row,
    '       store a formula into column C that derives […?? something].
    With LoadWS
        .Range("C:C").EntireColumn.Insert
        '  --
        Dim Formulas(1) '<<< If you do not have an "Option Base 1" _
                statement at the top of the module, that is equivalent to _
                "Dim Formulas(0 To 1)".
        Formulas(1) = "=IF(IFERROR(MATCH(A8,'ENG Employee List'!B:B,0)>0,FALSE)" _
                & ",""Authorised Resource"",""Unauthorised Resource"")"
        .Range("C8").Formula = Formulas 'That is apparently putting Formulas(0) _
                into the cell's property; and as it contains Empty, the intended _
                formula is not stored.  It's better to be more specific:
        .Range("C8").Formula = Formulas(1)
        LastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        .Range("C8:C" & LastRow).FillDown
        '   Note: Snowman55 found that if LastRow _was_ 8, that action removed the
        '       cell's formula.  So consider wrapping the assignment inside an If.
    End With
    '----   [The following is unrelated code, so it's better to keep it outside the With block.]
    Application.DisplayAlerts = True

The following notes are of not relevant to the missing formula problem, but speak to good coding:

1. For ease of understanding, include comments in your code.

2. You do not need to activate a worksheet and select a range from it to copy its data or paste data into it. For example:

    '----   Copy cells from some specific source.
    'Workbooks("Wkb1.xlsm").Sheets("Paste into here").Activate
    'Range("A1:ZA6").Select
    'Selection.Copy
    Workbooks("Wkb1.xlsm").Sheets("Paste into here").Range("A1:ZA6").Copy

And using an unqualified Range object is somewhat dangerous, as it refers to ActiveSheet, which a user could change while the procedure is executing. (FYI, you can include a "LoadWS.Activate" in later code, as needed.  I'm guessing that the "Load File" worksheet is present only in Wkb1.xlsm, which is the workbook containing this procedure. Right?)

3. The point of using a With block is to reduce the repetition of syntax (and possibly to improve performance). What follows "With" should be an object (typically; there is an exception) whose properties and/or methods you are going to reference within the block. "Worksheet("Load File").Select is not an object that exposes properties/methods. (I'm surprised the compiler does not flag that as an error.) That's why I changed your With block to start as:

With LoadWS
    .Range("…

which in this case is equivalent to:

With Worksheets("Load File")
    .Range("…

4. Your executable code starts with "Application.ScreenUpdating = False". While this procedure might be part of a series that is executed together, and the last in that series restores the setting, the absence of "Application.ScreenUpdating = True" at the bottom of this procedure might be an oversight.