VBA Code: how to use merged cell as range for a If and Formula statement

Hi guys,

First of all I'm sorry the title isn't too descritive (as asked) but I can't quite figure out how to put it!!

so,

I have an excel sheet that allows me to control a series of ongoing operations. These operations consist in a series of events performed by diferent teams. In my sheet I have a control for the events (Collumn D in excel attached) that turns "finished whan all Team 'X' events are finished and shows "Ongoing" when the Team hasn't finished all events. (Please note that in the attached excel the control not working because the tickboxes are not correctly linked to the cells but is working just fine in my excel sheet!)

The problem is that I wanted to introduce a new macro that inserts the following formula in column A:

IF(AND(D3="Finished";D4="Finished";D5="Finished";D6="Finished");"Operation Closed";"Operation Ongoing")

And I can't quite figure it out as the range for the IF statement might be variant allthought it's always the same as the range of the merged cells in column A!!

The attached file might give you a better idea of what I mean:
Book3.xlsx

Can anyone please help on this code??

Many thanks

Thank you PCI. It was a great first help, but it still doesn't consider the merged cells range as variant.
Consider the attached file:
Book3.xlsm

For operation B, the operation is being deemed as "Operation Closed" even though Team E hasn't finnish all events. Similarly, operation C is being deemed as "Operation Ongoing" even though teams A to C have all finished their events.

Thus, the code you provided does almost exactly what I wanted, but the trick is to know if we can program the IF AND statement range equal to the range of the merged cells! Anyone can help?

Many Thanks

Originally Posted by Acosta

Thank you PCI. It was a great first help, but it still doesn't consider the merged cells range as variant.
Consider the attached file:
Attachment 356376

For operation B, the operation is being deemed as "Operation Closed" even though Team E hasn't finnish all events. Similarly, operation C is being deemed as "Operation Ongoing" even though teams A to C have all finished their events.

Thus, the code you provided does almost exactly what I wanted, but the trick is to know if we can program the IF AND statement range equal to the range of the merged cells! Anyone can help?

Many Thanks

For Operation B... D11 is not in the Formula so Team E is not counted. A - D are Finished so Operation Closed.

For Operation C... Team E from Operation B (Cell D11) is included in the Formula. Team E not Finished so Operation Ongoing.

Works Fine or?

Regards

JRidge

PCI,

I think that's exactly it, but I haven't tested it through!! Very Ingenious... Thanks!

I just had to add a -1 at the end of NbRow= ...

Let me have a definitive comment on your suggestion tomorrow!

Thanks again

PCI,

2 quick adaptations and the code is doing exactly what I wanted:

1) While (I < LastRow) becomes While (I <= LastRow)

2) The - 1 goes in ... SUMPRODUCT(--(RC[3]:R[" & NbRow -1& "]... and the range is just perfect!

Many thanks for the help... great job