Worksheet selection change formula entry

[ajm]

I am trying to enter the following formula into cells on a worksheet change event but it errors out.

formula to input is: "=IF(H$10="",($H$8/12*K$8),($H$8/12*K$8)+H$10)"

I have used the following format to do this:

   Case "Post Tender Activity"
            .Range("J4:M4,o4:r4").Formula = "=0"
            Target.Offset(0, 12).Formula = "=IF(H$10="",($H$8/12*K$8),($H$8/12*K$8)+H$10)"
            Target.Offset(0, 13).Formula = "=IF(I$10="",($H$8/12*L$8),($H$8/12*L$8)+I$10)"

when selecting "Post Tender Activity", the first line clears the range preceding the cells where the formula is to reside, and then it errors out on the line beginning Target.Offset(0,12) with the message "Application defined or Object defined error".

If i change out the formula bit to simply activate the cell, it works a treat.

 

[ajm]

smitty, the sheet change is causing problems.

can you see anything wrong with what i have here:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

'///shifts savings depending on Status - Est>Target>Banked and back again.

    Dim shtFirst%, shtLast%
    shtFirst = Worksheets("First").Index
    shtLast = Worksheets("Last").Index
    If ActiveSheet.Index <= shtFirst Then Exit Sub
    If ActiveSheet.Index >= shtLast Then Exit Sub
    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Range("$I$4")) Is Nothing Then Exit Sub

    With Application
        .ScreenUpdating = False
        '.Unprotect
        Select Case Target.Value

        Case "Preparation"
            With Sh
                .Range("$D$4").Value = 1
                .Range("$J$4:$M$4,$T$4:$W$4").Value = 0
                .Range("$P$4").Formula = "=IF($G$8=0,0,IF($H$10="""",($H$8/12*$K$8),($H$8/12*$K$8)+$H$10))"
                .Range("$Q$4").Formula = "=IF($G$8=0,0,IF($I$10="""",($H$8/12*$L$8),($H$8/12*$L$8)+$I$10))"
            End With

        Case "RFx"
            With Sh
                .Range("$D$4").Value = 1
                .Range("$J$4:$M$4,$T$4:$W$4").Value = 0
                .Range("$P$4").Formula = "=IF($G$8=0,0,IF($H$10="""",($H$8/12*$K$8),($H$8/12*$K$8)+$H$10))"
                .Range("$Q$4").Formula = "=IF($G$8=0,0,IF($I$10="""",($H$8/12*$L$8),($H$8/12*$L$8)+$I$10))"
            End With

        Case "Negotiate"
            With Sh
                .Range("$D$4").Value = 1
                .Range("$J$4:$M$4,$T$4:$W$4").Value = 0
                .Range("$P$4").Formula = "=IF($G$8=0,0,IF($H$10="""",($H$8/12*$K$8),($H$8/12*$K$8)+$H$10))"
                .Range("$Q$4").Formula = "=IF($G$8=0,0,IF($I$10="""",($H$8/12*$L$8),($H$8/12*$L$8)+$I$10))"
            End With

        Case "Approval"
            With Sh
                .Range("$D$4").Value = 1
                .Range("$J$4:$M$4,$T$4:$W$4").Value = 0
                .Range("$P$4").Formula = "=IF($G$8=0,0,IF($H$10="""",($H$8/12*$K$8),($H$8/12*$K$8)+$H$10))"
                .Range("$Q$4").Formula = "=IF($G$8=0,0,IF($I$10="""",($H$8/12*$L$8),($H$8/12*$L$8)+$I$10))"
            End With

        Case "Completed"
            With Sh
                .Range("$D$4").Value = 1
                .Range("$J$4:$M$4,$O$4:$R$4").Value = 0
                .Range("$U$4").Formula = "=IF($G$8=0,0,IF($H$10="""",($H$8/12*$K$8),($H$8/12*$K$8)+$H$10))"
                .Range("$V$4").Formula = "=IF($G$8=0,0,IF($I$10="""",($H$8/12*$L$8),($H$8/12*$L$8)+$I$10))"
            End With

        Case "Savings Validation"
            With Sh
                .Range("$D$4").Value = 1
                .Range("$J$4:$M$4,$O$4:$R4$").Value = 0
                .Range("$U$4").Formula = "=IF($G$8=0,0,IF($H$10="""",($H$8/12*$K$8),($H$8/12*$K$8)+$H$10))"
                .Range("$V$4").Formula = "=IF($G$8=0,0,IF($I$10="""",($H$8/12*$L$8),($H$8/12*$L$8)+$I$10))"
            End With

        Case "Closed"
            With Sh
                .Range("$D$4").Value = 0
                .Range("$J$4:$M$4,$O$4:$R$4,$T$4:$W$4").Value = 0
            End With

        Case "On Hold"
            With Sh
                .Range("$J$4:$M$4,$T$4:$W$4").Value = 0
                .Range("$P$4").Formula = "=IF($G$8=0,0,IF($H$10="""",($H$8/12*$K$8),($H$8/12*$K$8)+$H$10))"
                .Range("$Q$4").Formula = "=IF($G$8=0,0,IF($I$10="""",($H$8/12*$L$8),($H$8/12*$L$8)+$I$10))"
            End With

        Case "Not Started"
            With Sh
                .Range("$D$4").Value = 3
                .Range("$O$4:$R$4,$T$4:$W$4").Value = 0
                .Range("$K$4").Formula = "=IF($G$8=0,0,IF($H$10="""",($H$8/12*$K$8),($H$8/12*$K$8)+$H$10))"
                .Range("$L$4").Formula = "=IF($G$8=0,0,IF($I$10="""",($H$8/12*$L$8),($H$8/12*$L$8)+$I$10))"
            End With

        End Select
        .Calculate
        '.Protect
        .ScreenUpdating = True
    End With
End Sub

i get the method intersect error now each time i try to close.