FormulaArray autofill doesn't show correct value

[scott.s.fower]

The VBA code correctly inputs the formula into the desired cell but when it autofills to desired destination the value showing is the same as in cell G7. If I manually go to the cells and F2 then Ctrl+Shift+Enter the correct value shows up

...
    Dim PMPT As String
    Dim PA As String
    Dim PID As String
    PMPT = ActiveWorkbook.Name
    PA = Sheets(2).Name
    Sheets(PA).Select
    Range(Range("B3"), Range("B3").End(xlDown)).Name = "PIDTrim"
    Range(Range("C3"), Range("C3").End(xlDown)).Name = "Whse"
    Range(Range("E3"), Range("E3").End(xlDown)).Name = "BOMNS"
...
    LastFRow = CStr(Range("F" & CStr(Application.Rows.Count)).End(xlUp).Row)
    
    Range("G7").Select
    ActiveCell.FormulaArray = "=INDEX('" & PMPT & "'!BOMNS,MATCH(RC[-2]&RC[12],'" & PMPT & "'!PIDTrim & '" & PMPT & "'!Whse,0))"

    Range("G7").Select
    Selection.AutoFill Destination:=Range("G7", Range("G" & LastFRow)), Type:=xlFormula

[gyclone]

Are the formulas in each cell right (containing proper relative cell references) and just the values are wrong? If so, the worksheet isn't calculating after you fill the formulas, probably meaning that calculation is set to Manual. You can either set back to autocalculate or you can calculate via the ribbon or in you macro:

ActiveSheet.Calculate

[scott.s.fower]

Thankyou, that was it, somehow the global setting in Excel were set to manual calculation, I changed it back to auto.
I will remember the ActiveSheet.Calculate though.

[scott.s.fower]

I did solve the problem another way that circumvents the calculation problem by using a loop

    Dim Firstrow As Long
    Dim Lastrow As Long
    Dim Lrow As Long
    Dim CalcMode As Long
    Dim ViewMode As Long

    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    'We use the ActiveSheet but you can replace this with
    'Sheets("MySheet")if you want
    With ActiveSheet

        'We select the sheet so we can change the window view
        .Select

        'If you are in Page Break Preview Or Page Layout view go
        'back to normal view, we do this for speed
        ViewMode = ActiveWindow.View
        ActiveWindow.View = xlNormalView

        'Turn off Page Breaks, we do this for speed
        .DisplayPageBreaks = False

        'Set the first and last row to loop through
        Firstrow = .UsedRange.Cells(1).Row
        Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

        'We loop from Lastrow to Firstrow (bottom to top)
        For Lrow = Lastrow - 1 To Firstrow Step -1

        If .Cells(Lrow, "G").Value = "" Then .Cells(Lrow, "G").FormulaArray = "=INDEX('" & PMPT & "'!BOMNS,MATCH(RC[-2]&RC[12],'" & PMPT & "'!PIDTrim & '" & PMPT & "'!Whse,0))"
        If Lrow = 6 Then End
        Next Lrow

    End With

    ActiveWindow.View = ViewMode
    With Application
        .ScreenUpdating = True
        .Calculation = CalcMode
    End With

[gyclone]

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

That's where you're setting to manual calculation. Just setting back to auto, like at the end of your code, doesn't force calculation until the NEXT time something changes.

[scott.s.fower]

Ok, thanks, can I just add Calculate to the end of that section to force calculation? because the next lines of code will copy and paste special values, if there are some formulas that aren't calculated they will be pasted as values with the incorrect value

[gyclone]

Yep, just do the ActiveSheet.Calculate before the copy/paste and you should be good.