• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Help Required - Changes in Rates & Percentages based on other cell

Khalid NGO

Khalid NGO

Excel Ninja
Hello Forum,
Greetings & Good Day...

I am looking for a VBA solution, and need your help again after a long time :)

Case is, we offer discount percentages to our customers, based on Trade Price (TP) Column G.
We somehow managed with 2 different workings highlighted in different color.

RED AREA (Column C & D)
We enter RATES Manually in Colunm C to see what will be the percentage in Column D.

GREEN AREA (Column E & F)
We enter PERCENTAGES manually in Column F to see what will be the rates in Column E.

This all working fine in separate columns, but now my boss is asking that he wants just 2 working column where he enters either RATES say 69.70 for Product-1, PERCENTAGE i.e. 59.00 should be shown in Column D.

and vice versa if he enters PERCENTAGE say 59.00 for the same product, RATES i.e. 69.70 should be updated in Column C.

Sample version is attached herewith.
Any or all help is appreciated.

Regards,

Khalid
 

Attachments

One question Sir,
is it possible to automatically update RATES and % if we change the values of T.P. ?
Currently I am doing F2 > Enter for this.
 
Hi Khalid ,

There is a problem with this ; since both the rate and the percent value are manually entered or calculated using code and not a formula , how can the code decide which is to be fixed , and which is to be recalculated ?

For instance , with a TP of 170 , we have the combination of 69.70 and 59 % ; now , when you change the TP to 180 , do we retain the 69.70 and recalculate the 59 % to a new percent value , or do we retain the 59 % and recalculate the 69.70 to a new rate ?

Obviously , depending on which we choose , the results will be different.

Narayan
 
Oh, now i see how it works, you are right Sir, I can't think so logically in VBA :(

No problem, I will manage with F2 Enter (if there is no way)

Thank you again Sir.
 
Khalid NGO said:
No problem, I will manage with F2 Enter (if there is no way)
Click to expand...

@ Khalid NGO

You might not understand what NARAYANK991 said.

For instance , with a TP of 170 , we have the combination of 69.70 and 59 % ; now , when you change the TP to 180 , do we retain the 69.70 and recalculate the 59 % to a new percent value , or do we retain the 59 % and recalculate the 69.70 to a new rate ?
Click to expand...

If TP will be change then what will be the changed value of rate & %.

Pls share the new scenario when TP will be changed.
 
Hi Deepak ,

I think Khalid has understood me very well ! That is why he has mentioned that he will continue with F2. If the user presses F2 , they have a choice to decide whether they will select the Rate and press F2 , or they will select the Percent value and press F2. The code cannot make this selection on its own.

Narayan
 
@ NARAYANK991

What i understand is that,

Upon TP Change...
  1. If rate will same then % would be changed.
  2. If % will same then rate would be changed.
  3. Both will changed based on the change (% Growth/Degrowth of TP )
Code can do the same!!
 
Deepak Sir, actually I thought Rate & % will auto update if we change the TP, but after clarification from Narayan Sir i understood that both are dependent on existing TP.

Your points are valid but I am not sure about the selection of my boss (Rate or %), now I have to convince my boss for F2 Enter if the TP is changed.

Thank you.
 
You must log in or register to reply here.
Back
Top