Entering a formula depending on criteria of cell

Your problem sounds circular in nature? Column A is dependent upon Column D but you want to put a formula in column D if there is something in column A?

You can try the following in D2 but I'm not sure I fully understand your problem.

=If(isblank(a2)=true,"",[some formula])

Thank you fro that bit of code, I followed your instructions, but have code already on that sheet. So i tried to fit it in the code already there and its not working, I dunno if its the way i have entered i.e. in wrong order or where the formula is not working cause its an Array..

i.e. has {} back and front, as its was original enetred in as a array into the cell.

Code for worksheet as it is at the moment

Private Sub Worksheet_Change(ByVal Target As Range)

Dim cell 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)

 For Each cell In Target
        If Not Intersect(cell, Range("A2:A10000")) Is Nothing Then _
            Cells(cell.Row, "E") = "{=MAX(IF(ISNUMBER(0+MID(D13,1,ROW($1:$4))),0+MID(D13,1,ROW($1:$4))))+IF(ISNUMBER(MATCH(RIGHT(D13,1),$X$2:$X$27,0)),VLOOKUP(RIGHT(D13,1),$X$2:$Y$27,2,0)/10000,0)}"
    Next cell
    
End If
Application.EnableEvents = True

End Sub

Thanks

G

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

Can you attach a sample workbook? Always helps.

Since you can hardly change more than one cell at a time, try this:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("d2:d10000")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Offset(, 1).FormulaArray = "=MAX(IF(ISNUMBER(0+MID(D3,1,ROW($1:$4))),0+MID($D3,1,ROW($1:$4))))+IF(ISNUMBER(MATCH(RIGHT(D3,1),$X$2:$X$27,0)),VLOOKUP(RIGHT(D3,1),$X$2:$Y$27,2,0)/10000,0)"
Application.EnableEvents = True

End Sub

Nope that produced the same result as i get at the moment...

Brilliant !!!!

You are a god send JB, working perfectly, so far, need a little more testing to double check it.

That one problem sorted, now onto the next

G