Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 16
Default Maintaining a cells value once a target is achieved

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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Maintaining a cells value once a target is achieved

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 InsertModule, 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" wrote:
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","")


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 16
Default Maintaining a cells value once a target is achieved

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


"Max" wrote:

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 InsertModule, 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" wrote:
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","")


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Maintaining a cells value once a target is achieved

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" wrote:
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.


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 16
Default Maintaining a cells value once a target is achieved

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!
--
Thanks Max, I had no idea.
cliff18


"Max" wrote:

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" wrote:
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.




  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 16
Default Maintaining a cells value once a target is achieved

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!
--
Cheers
cliff18


"cliff18" wrote:

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!
--
Thanks Max, I had no idea.
cliff18


"Max" wrote:

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" wrote:
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.


  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 16
Default Maintaining a cells value once a target is achieved

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.

--
Cheers
cliff18


"cliff18" wrote:

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!
--
Cheers
cliff18


"cliff18" wrote:

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!
--
Thanks Max, I had no idea.
cliff18


"Max" wrote:

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" wrote:
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.

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Maintaining a cells value once a target is achieved

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
---


  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 16
Default Maintaining a cells value once a target is achieved

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!
--
Cheers
cliff18


"Max" wrote:

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
---



  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Maintaining a cells value once a target is achieved

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
---


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Target cell reference moves when target is cut and pasted Illya Teideman Excel Discussion (Misc queries) 5 May 31st 07 11:34 AM
Maintaining in-doc hyperlinks when rows/cells change Ittai Gilat Excel Discussion (Misc queries) 4 December 17th 06 12:48 PM
Target Cells Bohdon Excel Worksheet Functions 1 February 23rd 06 10:18 PM
Maintaining cell references when inserting cells elsewhere Stephen Jefferson Excel Discussion (Misc queries) 3 August 5th 05 09:30 PM
merging cells and maintaining the fonts size of each cell dick Excel Discussion (Misc queries) 4 June 29th 05 11:04 AM


All times are GMT +1. The time now is 04:12 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"