|
Altering macro code
I'm trying to alter part of a code given to me for running a macro by a
member but have had no luck seaching the help files or trial & error to get it to work. The code works fine as it is, but I'd like to change the figure "2" (which represents 50%) to cell "M5" which allows user input to adjust the percentage. So from: /2+RC[8] to: /M5+RC[8] Working code below: Sub ResRetr7() Range("M7").FormulaR1C1 = _ "=IF(OR(AND(RC[5]=""H"",(RC[-9]<=((RC[7]-RC[8])/2+RC[8])))),""Yes"",IF(OR(AND(RC[5]=""I"",(RC[-9]=((RC[8]-RC[7])/2+RC[7])))),""Yes"",IF(OR(AND(RC[5]=""H"",(RC[-9]((RC[7]-RC[8])/2+RC[8])))),""Wait"",IF(OR(AND(RC[5]=""I"",(RC[-9]<((RC[8]-RC[7])/2+RC[7])))),""Wait"",""""))))" End Sub Thanks in advance. -- Cheers cliff18 |
Altering macro code
M5 in R1C1 reference style is R5C13
So just change the 2's that should always point to M5 to R5C13. R5C13 is like $M$5 in A1 reference style. cliff18 wrote: I'm trying to alter part of a code given to me for running a macro by a member but have had no luck seaching the help files or trial & error to get it to work. The code works fine as it is, but I'd like to change the figure "2" (which represents 50%) to cell "M5" which allows user input to adjust the percentage. So from: /2+RC[8] to: /M5+RC[8] Working code below: Sub ResRetr7() Range("M7").FormulaR1C1 = _ "=IF(OR(AND(RC[5]=""H"",(RC[-9]<=((RC[7]-RC[8])/2+RC[8])))),""Yes"",IF(OR(AND(RC[5]=""I"",(RC[-9]=((RC[8]-RC[7])/2+RC[7])))),""Yes"",IF(OR(AND(RC[5]=""H"",(RC[-9]((RC[7]-RC[8])/2+RC[8])))),""Wait"",IF(OR(AND(RC[5]=""I"",(RC[-9]<((RC[8]-RC[7])/2+RC[7])))),""Wait"",""""))))" End Sub Thanks in advance. -- Cheers cliff18 -- Dave Peterson |
Altering macro code
Oh no! Row 5 Column 13 of course!!
Thanks Dave, but i still have a problem. I assume I can't pull a % figure from the cell into the code. At least not the way I'm trying to do it, using 50% in M5. It works fine if I re-formate the M5 cell from percent back to numbers and insert 2 for instance. That's not too hard for 50% but when I'm looking for say 23.6% it's a bit of a problem. Can you suggest how I may be able to do this? -- Cheers cliff18 "Dave Peterson" wrote: M5 in R1C1 reference style is R5C13 So just change the 2's that should always point to M5 to R5C13. R5C13 is like $M$5 in A1 reference style. cliff18 wrote: I'm trying to alter part of a code given to me for running a macro by a member but have had no luck seaching the help files or trial & error to get it to work. The code works fine as it is, but I'd like to change the figure "2" (which represents 50%) to cell "M5" which allows user input to adjust the percentage. So from: /2+RC[8] to: /M5+RC[8] Working code below: Sub ResRetr7() Range("M7").FormulaR1C1 = _ "=IF(OR(AND(RC[5]=""H"",(RC[-9]<=((RC[7]-RC[8])/2+RC[8])))),""Yes"",IF(OR(AND(RC[5]=""I"",(RC[-9]=((RC[8]-RC[7])/2+RC[7])))),""Yes"",IF(OR(AND(RC[5]=""H"",(RC[-9]((RC[7]-RC[8])/2+RC[8])))),""Wait"",IF(OR(AND(RC[5]=""I"",(RC[-9]<((RC[8]-RC[7])/2+RC[7])))),""Wait"",""""))))" End Sub Thanks in advance. -- Cheers cliff18 -- Dave Peterson |
Altering macro code
If you want the equivalent of dividing by 2 by putting 50% in M5, then you'll
want to multiply by M5 (R5C13). I'm not sure what you want when you have 23.6% in M5. Maybe multiply??? cliff18 wrote: Oh no! Row 5 Column 13 of course!! Thanks Dave, but i still have a problem. I assume I can't pull a % figure from the cell into the code. At least not the way I'm trying to do it, using 50% in M5. It works fine if I re-formate the M5 cell from percent back to numbers and insert 2 for instance. That's not too hard for 50% but when I'm looking for say 23.6% it's a bit of a problem. Can you suggest how I may be able to do this? -- Cheers cliff18 "Dave Peterson" wrote: M5 in R1C1 reference style is R5C13 So just change the 2's that should always point to M5 to R5C13. R5C13 is like $M$5 in A1 reference style. cliff18 wrote: I'm trying to alter part of a code given to me for running a macro by a member but have had no luck seaching the help files or trial & error to get it to work. The code works fine as it is, but I'd like to change the figure "2" (which represents 50%) to cell "M5" which allows user input to adjust the percentage. So from: /2+RC[8] to: /M5+RC[8] Working code below: Sub ResRetr7() Range("M7").FormulaR1C1 = _ "=IF(OR(AND(RC[5]=""H"",(RC[-9]<=((RC[7]-RC[8])/2+RC[8])))),""Yes"",IF(OR(AND(RC[5]=""I"",(RC[-9]=((RC[8]-RC[7])/2+RC[7])))),""Yes"",IF(OR(AND(RC[5]=""H"",(RC[-9]((RC[7]-RC[8])/2+RC[8])))),""Wait"",IF(OR(AND(RC[5]=""I"",(RC[-9]<((RC[8]-RC[7])/2+RC[7])))),""Wait"",""""))))" End Sub Thanks in advance. -- Cheers cliff18 -- Dave Peterson -- Dave Peterson |
Altering macro code
Yep, your correct with part of it as I just found out I changed the / to *
and now functions ok, except I can only get one of the calculations to work as I want it. So this part works ok: IF(OR(AND(RC[5]=""I"",(RC[-9]=((RC[8]-RC[7])*R5C13+RC[7])))),""Yes"", But this doesn't: "=IF(OR(AND(RC[5]=""H"",(RC[-9]<=((RC[7]-RC[8])*R5C13+RC[8])))),""Yes"", I'll try to explain what I'm trying to do in an example below. If RC[5] = "I" and RC[-9] = 0.7030, and RC[8] = 0.7100, and RC[7] = 0.7000' then if I enter 31% into R5C13, then M7 displays "Wait", If I change it to 30% then M7 displays "Yes" and this works fine, however, If RC[5] = "H", and RC[-9] = 0.7070, and RC[8] = 0.7000, and RC[7] = 0.7100, and I enter 30% into R5C13, I'm wanting cell M7 to display "Yes" but it doesn't. It will only display "Yes" when the percentage is increased to 70%, as it's only calculating from the bottom up. Hope that's clear. Is it possible to get the % to calculate down from the higher number also without changing the % to do so? Please ask if you require more and think you can help. -- Cheers cliff18 "cliff18" wrote: Oh no! Row 5 Column 13 of course!! Thanks Dave, but i still have a problem. I assume I can't pull a % figure from the cell into the code. At least not the way I'm trying to do it, using 50% in M5. It works fine if I re-formate the M5 cell from percent back to numbers and insert 2 for instance. That's not too hard for 50% but when I'm looking for say 23.6% it's a bit of a problem. Can you suggest how I may be able to do this? -- Cheers cliff18 -- Cheers cliff18 "Dave Peterson" wrote: If you want the equivalent of dividing by 2 by putting 50% in M5, then you'll want to multiply by M5 (R5C13). I'm not sure what you want when you have 23.6% in M5. Maybe multiply??? cliff18 wrote: Oh no! Row 5 Column 13 of course!! Thanks Dave, but i still have a problem. I assume I can't pull a % figure from the cell into the code. At least not the way I'm trying to do it, using 50% in M5. It works fine if I re-formate the M5 cell from percent back to numbers and insert 2 for instance. That's not too hard for 50% but when I'm looking for say 23.6% it's a bit of a problem. Can you suggest how I may be able to do this? -- Cheers cliff18 "Dave Peterson" wrote: M5 in R1C1 reference style is R5C13 So just change the 2's that should always point to M5 to R5C13. R5C13 is like $M$5 in A1 reference style. cliff18 wrote: I'm trying to alter part of a code given to me for running a macro by a member but have had no luck seaching the help files or trial & error to get it to work. The code works fine as it is, but I'd like to change the figure "2" (which represents 50%) to cell "M5" which allows user input to adjust the percentage. So from: /2+RC[8] to: /M5+RC[8] Working code below: Sub ResRetr7() Range("M7").FormulaR1C1 = _ "=IF(OR(AND(RC[5]=""H"",(RC[-9]<=((RC[7]-RC[8])/2+RC[8])))),""Yes"",IF(OR(AND(RC[5]=""I"",(RC[-9]=((RC[8]-RC[7])/2+RC[7])))),""Yes"",IF(OR(AND(RC[5]=""H"",(RC[-9]((RC[7]-RC[8])/2+RC[8])))),""Wait"",IF(OR(AND(RC[5]=""I"",(RC[-9]<((RC[8]-RC[7])/2+RC[7])))),""Wait"",""""))))" End Sub Thanks in advance. -- Cheers cliff18 -- Dave Peterson -- Dave Peterson |
Altering macro code
First, I understand things better in A1 reference style.
So your (modified) formula: =IF(OR(AND(RC[5]=""H"",(RC[-9]<=((RC[7]-RC[8])*R5C13+RC[8])))),""Yes"",""No"") =IF(OR(AND(R7="H",(D7<=((T7-U7)*$M$5+U7)))),"Yes","No") R7 = H D7 = .707 T7 = .71 U7 = .7 M5 = .3 When I plop them into your formula: =IF(OR(AND("H"="H",(.707<=((.71-.7)*.3+.7)))),"Yes","No") =IF(OR(AND("H"="H",(.707<=((.01)*.3+.7)))),"Yes"," No") =IF(OR(AND("H"="H",(.707<=(.003+.7)))),"Yes","No") =IF(OR(AND("H"="H",(.707<=.703))),"Yes","No") =if(or(and(true,false)),"yes","no") =if(or(false),"yes","no") =if(false,"yes","no") "no" ======= I don't understand your use of or(and()). If you want both of those requirements to be true, then use =and(). If you want either of those requirements to be true, then use =or(). Don't use both. cliff18 wrote: Yep, your correct with part of it as I just found out I changed the / to * and now functions ok, except I can only get one of the calculations to work as I want it. So this part works ok: IF(OR(AND(RC[5]=""I"",(RC[-9]=((RC[8]-RC[7])*R5C13+RC[7])))),""Yes"", But this doesn't: "=IF(OR(AND(RC[5]=""H"",(RC[-9]<=((RC[7]-RC[8])*R5C13+RC[8])))),""Yes"", I'll try to explain what I'm trying to do in an example below. If RC[5] = "I" and RC[-9] = 0.7030, and RC[8] = 0.7100, and RC[7] = 0.7000' then if I enter 31% into R5C13, then M7 displays "Wait", If I change it to 30% then M7 displays "Yes" and this works fine, however, If RC[5] = "H", and RC[-9] = 0.7070, and RC[8] = 0.7000, and RC[7] = 0.7100, and I enter 30% into R5C13, I'm wanting cell M7 to display "Yes" but it doesn't. It will only display "Yes" when the percentage is increased to 70%, as it's only calculating from the bottom up. Hope that's clear. Is it possible to get the % to calculate down from the higher number also without changing the % to do so? Please ask if you require more and think you can help. -- Cheers cliff18 "cliff18" wrote: Oh no! Row 5 Column 13 of course!! Thanks Dave, but i still have a problem. I assume I can't pull a % figure from the cell into the code. At least not the way I'm trying to do it, using 50% in M5. It works fine if I re-formate the M5 cell from percent back to numbers and insert 2 for instance. That's not too hard for 50% but when I'm looking for say 23.6% it's a bit of a problem. Can you suggest how I may be able to do this? -- Cheers cliff18 -- Cheers cliff18 "Dave Peterson" wrote: If you want the equivalent of dividing by 2 by putting 50% in M5, then you'll want to multiply by M5 (R5C13). I'm not sure what you want when you have 23.6% in M5. Maybe multiply??? cliff18 wrote: Oh no! Row 5 Column 13 of course!! Thanks Dave, but i still have a problem. I assume I can't pull a % figure from the cell into the code. At least not the way I'm trying to do it, using 50% in M5. It works fine if I re-formate the M5 cell from percent back to numbers and insert 2 for instance. That's not too hard for 50% but when I'm looking for say 23.6% it's a bit of a problem. Can you suggest how I may be able to do this? -- Cheers cliff18 "Dave Peterson" wrote: M5 in R1C1 reference style is R5C13 So just change the 2's that should always point to M5 to R5C13. R5C13 is like $M$5 in A1 reference style. cliff18 wrote: I'm trying to alter part of a code given to me for running a macro by a member but have had no luck seaching the help files or trial & error to get it to work. The code works fine as it is, but I'd like to change the figure "2" (which represents 50%) to cell "M5" which allows user input to adjust the percentage. So from: /2+RC[8] to: /M5+RC[8] Working code below: Sub ResRetr7() Range("M7").FormulaR1C1 = _ "=IF(OR(AND(RC[5]=""H"",(RC[-9]<=((RC[7]-RC[8])/2+RC[8])))),""Yes"",IF(OR(AND(RC[5]=""I"",(RC[-9]=((RC[8]-RC[7])/2+RC[7])))),""Yes"",IF(OR(AND(RC[5]=""H"",(RC[-9]((RC[7]-RC[8])/2+RC[8])))),""Wait"",IF(OR(AND(RC[5]=""I"",(RC[-9]<((RC[8]-RC[7])/2+RC[7])))),""Wait"",""""))))" End Sub Thanks in advance. -- Cheers cliff18 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Altering macro code
As gratefull as I was to get the code, I'm with you on prefering A1 ref, so I
will change it to suit, including following your suggestions of removing the OR part of the argument, and see if I can get it to work. It appears I have corrected my other problem of trying to get the % to work from both the top down and bottom up by inserting another column with a formula to find the amount, as determined by the % requested, and then bringing this amount back into this formula. Thanks very much for your help on this Dave. -- Cheers cliff18 "Dave Peterson" wrote: First, I understand things better in A1 reference style. So your (modified) formula: =IF(OR(AND(RC[5]=""H"",(RC[-9]<=((RC[7]-RC[8])*R5C13+RC[8])))),""Yes"",""No"") =IF(OR(AND(R7="H",(D7<=((T7-U7)*$M$5+U7)))),"Yes","No") R7 = H D7 = .707 T7 = .71 U7 = .7 M5 = .3 When I plop them into your formula: =IF(OR(AND("H"="H",(.707<=((.71-.7)*.3+.7)))),"Yes","No") =IF(OR(AND("H"="H",(.707<=((.01)*.3+.7)))),"Yes"," No") =IF(OR(AND("H"="H",(.707<=(.003+.7)))),"Yes","No") =IF(OR(AND("H"="H",(.707<=.703))),"Yes","No") =if(or(and(true,false)),"yes","no") =if(or(false),"yes","no") =if(false,"yes","no") "no" ======= I don't understand your use of or(and()). If you want both of those requirements to be true, then use =and(). If you want either of those requirements to be true, then use =or(). Don't use both. cliff18 wrote: Yep, your correct with part of it as I just found out I changed the / to * and now functions ok, except I can only get one of the calculations to work as I want it. So this part works ok: IF(OR(AND(RC[5]=""I"",(RC[-9]=((RC[8]-RC[7])*R5C13+RC[7])))),""Yes"", But this doesn't: "=IF(OR(AND(RC[5]=""H"",(RC[-9]<=((RC[7]-RC[8])*R5C13+RC[8])))),""Yes"", I'll try to explain what I'm trying to do in an example below. If RC[5] = "I" and RC[-9] = 0.7030, and RC[8] = 0.7100, and RC[7] = 0.7000' then if I enter 31% into R5C13, then M7 displays "Wait", If I change it to 30% then M7 displays "Yes" and this works fine, however, If RC[5] = "H", and RC[-9] = 0.7070, and RC[8] = 0.7000, and RC[7] = 0.7100, and I enter 30% into R5C13, I'm wanting cell M7 to display "Yes" but it doesn't. It will only display "Yes" when the percentage is increased to 70%, as it's only calculating from the bottom up. Hope that's clear. Is it possible to get the % to calculate down from the higher number also without changing the % to do so? Please ask if you require more and think you can help. -- Cheers cliff18 "cliff18" wrote: Oh no! Row 5 Column 13 of course!! Thanks Dave, but i still have a problem. I assume I can't pull a % figure from the cell into the code. At least not the way I'm trying to do it, using 50% in M5. It works fine if I re-formate the M5 cell from percent back to numbers and insert 2 for instance. That's not too hard for 50% but when I'm looking for say 23.6% it's a bit of a problem. Can you suggest how I may be able to do this? -- Cheers cliff18 -- Cheers cliff18 "Dave Peterson" wrote: If you want the equivalent of dividing by 2 by putting 50% in M5, then you'll want to multiply by M5 (R5C13). I'm not sure what you want when you have 23.6% in M5. Maybe multiply??? cliff18 wrote: Oh no! Row 5 Column 13 of course!! Thanks Dave, but i still have a problem. I assume I can't pull a % figure from the cell into the code. At least not the way I'm trying to do it, using 50% in M5. It works fine if I re-formate the M5 cell from percent back to numbers and insert 2 for instance. That's not too hard for 50% but when I'm looking for say 23.6% it's a bit of a problem. Can you suggest how I may be able to do this? -- Cheers cliff18 "Dave Peterson" wrote: M5 in R1C1 reference style is R5C13 So just change the 2's that should always point to M5 to R5C13. R5C13 is like $M$5 in A1 reference style. cliff18 wrote: I'm trying to alter part of a code given to me for running a macro by a member but have had no luck seaching the help files or trial & error to get it to work. The code works fine as it is, but I'd like to change the figure "2" (which represents 50%) to cell "M5" which allows user input to adjust the percentage. So from: /2+RC[8] to: /M5+RC[8] Working code below: Sub ResRetr7() Range("M7").FormulaR1C1 = _ "=IF(OR(AND(RC[5]=""H"",(RC[-9]<=((RC[7]-RC[8])/2+RC[8])))),""Yes"",IF(OR(AND(RC[5]=""I"",(RC[-9]=((RC[8]-RC[7])/2+RC[7])))),""Yes"",IF(OR(AND(RC[5]=""H"",(RC[-9]((RC[7]-RC[8])/2+RC[8])))),""Wait"",IF(OR(AND(RC[5]=""I"",(RC[-9]<((RC[8]-RC[7])/2+RC[7])))),""Wait"",""""))))" End Sub Thanks in advance. -- Cheers cliff18 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Altering macro code
There's no reason to change your VBA code. I just meant that when I'm looking
at a formula, I like to see the A1 reference style. But it doesn't bother me using .formular1c1 in code. There are lots of times that this is the easiest approach to use. cliff18 wrote: As gratefull as I was to get the code, I'm with you on prefering A1 ref, so I will change it to suit, including following your suggestions of removing the OR part of the argument, and see if I can get it to work. It appears I have corrected my other problem of trying to get the % to work from both the top down and bottom up by inserting another column with a formula to find the amount, as determined by the % requested, and then bringing this amount back into this formula. Thanks very much for your help on this Dave. -- Cheers cliff18 "Dave Peterson" wrote: First, I understand things better in A1 reference style. So your (modified) formula: =IF(OR(AND(RC[5]=""H"",(RC[-9]<=((RC[7]-RC[8])*R5C13+RC[8])))),""Yes"",""No"") =IF(OR(AND(R7="H",(D7<=((T7-U7)*$M$5+U7)))),"Yes","No") R7 = H D7 = .707 T7 = .71 U7 = .7 M5 = .3 When I plop them into your formula: =IF(OR(AND("H"="H",(.707<=((.71-.7)*.3+.7)))),"Yes","No") =IF(OR(AND("H"="H",(.707<=((.01)*.3+.7)))),"Yes"," No") =IF(OR(AND("H"="H",(.707<=(.003+.7)))),"Yes","No") =IF(OR(AND("H"="H",(.707<=.703))),"Yes","No") =if(or(and(true,false)),"yes","no") =if(or(false),"yes","no") =if(false,"yes","no") "no" ======= I don't understand your use of or(and()). If you want both of those requirements to be true, then use =and(). If you want either of those requirements to be true, then use =or(). Don't use both. cliff18 wrote: Yep, your correct with part of it as I just found out I changed the / to * and now functions ok, except I can only get one of the calculations to work as I want it. So this part works ok: IF(OR(AND(RC[5]=""I"",(RC[-9]=((RC[8]-RC[7])*R5C13+RC[7])))),""Yes"", But this doesn't: "=IF(OR(AND(RC[5]=""H"",(RC[-9]<=((RC[7]-RC[8])*R5C13+RC[8])))),""Yes"", I'll try to explain what I'm trying to do in an example below. If RC[5] = "I" and RC[-9] = 0.7030, and RC[8] = 0.7100, and RC[7] = 0.7000' then if I enter 31% into R5C13, then M7 displays "Wait", If I change it to 30% then M7 displays "Yes" and this works fine, however, If RC[5] = "H", and RC[-9] = 0.7070, and RC[8] = 0.7000, and RC[7] = 0.7100, and I enter 30% into R5C13, I'm wanting cell M7 to display "Yes" but it doesn't. It will only display "Yes" when the percentage is increased to 70%, as it's only calculating from the bottom up. Hope that's clear. Is it possible to get the % to calculate down from the higher number also without changing the % to do so? Please ask if you require more and think you can help. -- Cheers cliff18 "cliff18" wrote: Oh no! Row 5 Column 13 of course!! Thanks Dave, but i still have a problem. I assume I can't pull a % figure from the cell into the code. At least not the way I'm trying to do it, using 50% in M5. It works fine if I re-formate the M5 cell from percent back to numbers and insert 2 for instance. That's not too hard for 50% but when I'm looking for say 23.6% it's a bit of a problem. Can you suggest how I may be able to do this? -- Cheers cliff18 -- Cheers cliff18 "Dave Peterson" wrote: If you want the equivalent of dividing by 2 by putting 50% in M5, then you'll want to multiply by M5 (R5C13). I'm not sure what you want when you have 23.6% in M5. Maybe multiply??? cliff18 wrote: Oh no! Row 5 Column 13 of course!! Thanks Dave, but i still have a problem. I assume I can't pull a % figure from the cell into the code. At least not the way I'm trying to do it, using 50% in M5. It works fine if I re-formate the M5 cell from percent back to numbers and insert 2 for instance. That's not too hard for 50% but when I'm looking for say 23.6% it's a bit of a problem. Can you suggest how I may be able to do this? -- Cheers cliff18 "Dave Peterson" wrote: M5 in R1C1 reference style is R5C13 So just change the 2's that should always point to M5 to R5C13. R5C13 is like $M$5 in A1 reference style. cliff18 wrote: I'm trying to alter part of a code given to me for running a macro by a member but have had no luck seaching the help files or trial & error to get it to work. The code works fine as it is, but I'd like to change the figure "2" (which represents 50%) to cell "M5" which allows user input to adjust the percentage. So from: /2+RC[8] to: /M5+RC[8] Working code below: Sub ResRetr7() Range("M7").FormulaR1C1 = _ "=IF(OR(AND(RC[5]=""H"",(RC[-9]<=((RC[7]-RC[8])/2+RC[8])))),""Yes"",IF(OR(AND(RC[5]=""I"",(RC[-9]=((RC[8]-RC[7])/2+RC[7])))),""Yes"",IF(OR(AND(RC[5]=""H"",(RC[-9]((RC[7]-RC[8])/2+RC[8])))),""Wait"",IF(OR(AND(RC[5]=""I"",(RC[-9]<((RC[8]-RC[7])/2+RC[7])))),""Wait"",""""))))" End Sub Thanks in advance. -- Cheers cliff18 -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 12:27 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com