Value of a Formula in a Sheet Macro

[driadominique]

Hiya,

I am trying to get a value of a formula to populate a cell when a change is made. I have it working so that the formula populates, but when another cell changes, it re-counts! What do I have to do to only insert the value?

This the line in question:

Target.Offset(0, 12).Formula = "=CountIf($M:$M,UNTESTED)"

Thanks in advance!

~Dria

 

[VoG]

Hello and welcome to MrExcel.

Try

With Target.Offset(0, 12)
    .Formula = "=CountIf($M:$M,UNTESTED)"
    .Value = .Value
End With

 

[driadominique]

Wow! That is absolutely perfect!

Thank you so much for the prompt reply. I just found this forum, but am very impressed so far. I will definitely recommend it.