Entering a formula depending on criteria of cell

Sorry a bit of a newwbie to this,

I have tried taking out the target.cells.count > 1 code but still not working.

The orginal formula works fine, just does not want to work within the exsiting code or understand the array... ??

G

This formula works great but as i said, my formula is an array, so i just cant enter it in. I have looked throught the internet on how to do this but they expect the formulaarray to follow a range.

I dont see the point in reposting this as a different question as it is part of the same problem. as its a formula i need to insert into a cell depending on a certain criteria

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Application.EnableEvents = False

    For Each cell In Target
        If Not Intersect(cell, Range("A2:A10000")) Is Nothing Then _
            Cells(cell.Row, "D") = "formula"
    Next cell

Application.EnableEvents = True
End Sub

Would like to say thanks to all that have help, I know you have better things to do than help us newwbies lol

cheers you are all stars

Adapting JB's code a little, something along these lines? I don't understand your previous code because it seems to contradict itself.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim cell As Range
Application.EnableEvents = False

    For Each cell In Target
        If Not Intersect(cell, Range("A2:A10000")) Is Nothing Then _
            Cells(cell.Row, "D").FormulaArray = "=SUM(A1:A10)"
    Next cell

Application.EnableEvents = True

End Sub

I know you have better things to do than help us newwbies lol

Not at all, it is the raison d'etre of this place and places like it.

Strange thing is i tried adding .formulaArray and it came up with errors.

the code which is contridicting it self is explained like this

I already have this code on my worksheet

Private Sub Worksheet_Change(ByVal Target As Range)


If Intersect(Target, Range("F2:f3000,g2:g3000,h2:I3000,l2:l3000,m2:m3000,n2:n3000,o2:o3000")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
If Application.WorksheetFunction.IsText(Target.Value) Then
Target.Value = UCase(Target.Value)


End If
Application.EnableEvents = True

End Sub

and what im trying to do if this code works

Private Sub Worksheet_Change(ByVal Target As Range)

Dim cell As Range
Application.EnableEvents = False

    For Each cell In Target
        If Not Intersect(cell, Range("A2:A10000")) Is Nothing Then _
            Cells(cell.Row, "D").FormulaArray = "=SUM(A1:A10)"
    Next cell

Application.EnableEvents = True

End Sub

is to try and to put the two together

G