| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| help with altering existing code | Excel Programming | |||
| altering URLs with code | Excel Programming | |||
| Altering code to accomodate empty space | Excel Programming | |||
| Help with altering a SaveAs macro . . . | Excel Programming | |||
| Help with altering a download macro | Excel Programming | |||
Hybrid Mode
