Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Target cell reference moves when target is cut and pasted | Excel Discussion (Misc queries) | |||
Maintaining in-doc hyperlinks when rows/cells change | Excel Discussion (Misc queries) | |||
Target Cells | Excel Worksheet Functions | |||
Maintaining cell references when inserting cells elsewhere | Excel Discussion (Misc queries) | |||
merging cells and maintaining the fonts size of each cell | Excel Discussion (Misc queries) |