Altering macro code

C

cliff18

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

Dave Peterson

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.

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

cliff18

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 said:
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.

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

Dave Peterson

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???
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 said:
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.

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

cliff18

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 said:
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 said:
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???
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 said:
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.
 
D

Dave Peterson

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.



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 said:
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 said:
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???
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

:

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

cliff18

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 said:
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.



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

:

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

Dave Peterson

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.


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 said:
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.



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

:

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

:

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

:

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top