Can't get Intersect command to copy formula

[mcomp72]

I have a sub that copies a formula into a group of cells. There are two options for what formula it will put in the cell, depending on which option button is selected by the user. It isn't working, and I'm not sure why.

Note: I don't want to call the cells by their Column Letter and Row Number, in case I insert columns and rows in the future. So I am calling the Columns by their Named Ranges and the rows by numbers that it reads from somewhere on the sheet.

Sub VarianceColumnCalc()

Dim FirstRow As Integer, LastRow As Integer

FirstRow = Range("A_first_row").Value
LastRow = Range("A_last_row").Value

Application.ScreenUpdating = False
Module7.UnprotectPage1ASheet

Select Case Application.Caller

    Case "EstimatedOptionButton"
        ThisWorkbook.Sheets("Page 1A").Intersect([P1A_Actual_Variance], Rows(FirstRow)).Formula = "=P1A_Actual-P1A_Bid_Estimated"
        
    Case "WorkingOptionButton"
        ThisWorkbook.Sheets("Page 1A").Intersect([P1A_Actual_Variance], Rows(FirstRow)).Formula = "=P1A_Actual-P1A_Working_Total"

End Select

ThisWorkbook.Sheets("Page 1A").Intersect([P1A_Actual_Variance], Rows(FirstRow & ":" & LastRow + 4)).Formula.FillDown

ThisWorkbook.Sheets("Page 1A").Range("P1A_Actual_VarianceTotal").Font.Bold = True

Module7.ProtectPage1ASheet
Application.ScreenUpdating = True

End Sub

It is throwing an error on the line directly after 'Case "EstimatedOptionButton"'. It says Object doesn't support this property or method. I assume it has something to do with Intersect? But I am not familiar enough with Intersect to know if the problem is my syntax or if it simply cannot paste formulas to cells using it?

 

[steve the fish]

This is how id write that intersect where Table1 is the name of the table:

With Sheets("Page 1A")
    Set rng = Intersect(.Range("Table1[[#All],[P1A_Actual_Variance]]"), .Rows(FirstRow))
    If Not rng Is Nothing Then rng.Value = "=P1A_Actual-P1A_Bid_Estimated"
End With

 

[mcomp72]

Thanks, Steve.

This morning I was Googling around looking for a solution, and I stumbled upon something else that worked. If I change the line:

ThisWorkbook.Sheets("Page 1A").Intersect([P1A_Actual_Variance], Rows(FirstRow)).Formula = "=P1A_Actual-P1A_Bid_Estimated"

To this:

Application.Intersect([P1A_Actual_Variance], Rows(FirstRow)).Formula = "=P1A_Actual-P1A_Bid_Estimated"

It works. So for this instance I am going to go with this. (But if you see any potential issue with doing it this way, please let me know.) But I see the logic behind the way you did it, and that could work, too.