Maintaining a cells value once a target is achieved

[cliff18]

I use Excel 2003.
I import changing data into one cell 'D7' in the form of a number. I use
information from other cells 'R7,T7,U7' to give me a target, and once the
target is achieved, the text "Yes" is automatically printed in another cell
'M7'.
I have been able to make this work ok using the formula below, but
unfortunatley once the figure rises above the target again, the text
disappears.
Does anyone know of a way that I can make the text remain in the cell once
the target is achieved until I manually reset it.

=IF(AND(R7="H",(D7<=((T7-U7)/2+U7))),"Yes","")

I would ppreciate any help.
Cheers
cliff18

 

[Max]

Try this on a spare copy

Right-click the sheet tab > choose "View Code"
Copy n paste the sub below into the code window (white space on right)
Press Alt+Q to get back to Excel
Test it out ...

'------
Private Sub Worksheet_Calculate()
If Range("M7").Value = "Yes" Then
Range("M7").Value = Range("M7").Value
End If
End Sub
'-----

And to re-instate the formula in M7 later (when it doesn't evaluate to
"Yes"), you could install the sub below in a regular module*, then run it in
Excel, via say, the Macro dialog (press Alt+F8)
*press Alt+F11 to go to VBE, click Insert>Module, copy n paste

Sub Put_In_M1()
Range("M7").FormulaR1C1 = _
"=IF(AND(RC[5]=""H"",(RC[-9]<=((RC[7]-RC[8])/2+RC[8]))),""Yes"","""")"
End Sub
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik

 

[cliff18]

Thanks Max, what you gave me worked perfectly, including reloading the
formula with the Macro!
The only thing I didn't mention is that I would like this formula to work
all the way down from row 7 (as in the formula I sent) down to row 20. I
thought I could work it out myself with what you gave me, but unfortunatley I
am just getting errors as I change the range as below.

'------
Private Sub Worksheet_Calculate()
If Range("M7:M20").Value = "Yes" Then
Range("M7:M20").Value = Range("M7:M20").Value
End If
End Sub
'-----

Sorry to ask again but is it possible to get this to work in this way.
--
Cheers
cliff18

Try this on a spare copy

Right-click the sheet tab > choose "View Code"
Copy n paste the sub below into the code window (white space on right)
Press Alt+Q to get back to Excel
Test it out ...

'------
Private Sub Worksheet_Calculate()
If Range("M7").Value = "Yes" Then
Range("M7").Value = Range("M7").Value
End If
End Sub
'-----

And to re-instate the formula in M7 later (when it doesn't evaluate to
"Yes"), you could install the sub below in a regular module*, then run it in
Excel, via say, the Macro dialog (press Alt+F8)
*press Alt+F11 to go to VBE, click Insert>Module, copy n paste

Sub Put_In_M1()
Range("M7").FormulaR1C1 = _
"=IF(AND(RC[5]=""H"",(RC[-9]<=((RC[7]-RC[8])/2+RC[8]))),""Yes"","""")"
End Sub
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---

I use Excel 2003.
I import changing data into one cell 'D7' in the form of a number. I use
information from other cells 'R7,T7,U7' to give me a target, and once the
target is achieved, the text "Yes" is automatically printed in another cell
'M7'.
I have been able to make this work ok using the formula below, but
unfortunately once the figure rises above the target again, the text
disappears.
Does anyone know of a way that I can make the text remain in the cell once
the target is achieved until I manually reset it.
=IF(AND(R7="H",(D7<=((T7-U7)/2+U7))),"Yes","")

 

[Max]

Tinkered around, this seems ok:

Private Sub Worksheet_Calculate()
Set c = Range("M7:M20")
For Each i In c
If i.Value = "Yes" Then
i.Value = i.Value
End If
Next
End Sub

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---

 

[cliff18]

If I could tinker like that I'd spend less time here, that's for sure!
After what you've given me, I simply changed the range in the macro to
("M7:M20") for reloading the formula and it all seems perfect!

 

[cliff18]

Sorry Max, but I found when I shut the application down and restart it I get
a 'Run-time error '13';' I can simply end it and it carries on so it's not a
big problem I guess.
I'll spend more time with it later.
Thanks again mate!

 

[cliff18]

For some reason, the code has changed itself back as below.

Private Sub Worksheet_Calculate()
Set c = Range("M7")
For Each i In c
If i.Value = "Yes" Then
i.Value = i.Value
End If
Next
End Sub

As soon as I try to change the range back to ("M7:M20") as I need it, Excel
freezes.
Does anyone know how I may overcome this.

 

[Max]

I'm unable to replicate the error / behaviour that you mention.
It works ok in my test file.

Try a new posting in excel.programming newsgroup
for better views from responders versed in vba.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---

 

[cliff18]

Sorry Max, the reason you were unable to replicate the error was that I had
not given you the relevant details. I extended/changed the code in the macro
to what I required, but didn't realise that that was creating the problem.
I continued this thread under
'Unable to correct VBA code' in Excel Programming,
as you suggested, and with help from you, others and some reseach, was able
to fix everything.
Anyone following this thread will find the final result there. I hope this
thread helps others. thankyou all!

 

[Max]

Glad you got it sorted out ok in .programming.
Thanks for feeding back here.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---