Update Values by a Percentage using VBA

[NickPDC]

Hi there guys,

I was hoping the community would be able to help me out a bit here.

I have a Workbook (attached), where the data within, is copied and pasted from an external source.

This data then needs to be updated with any price changes that may occur, this is the "Contract Price" in the O column.

This needs to be updateable by putting a percentage into the Increase Percent box at B4.

Say you have a contract price of £10, and you wanted up update this by 20%, would give the result of £12, and so on.

The "New Effective Date" value will also need to be pasted into the "Effective Date" column for the data.

The twist is this..

There is a drop down box at B6. This is basically needed to allocate the price change to a certain area of manufacture. Eg, Scrap Metal Surcharge etc.

So the Macro will need to calculate 20% of the "Contract Price", and then paste the percentage value increase, into the corresponding column (located between columns R and V).

If a percentage increase occurs, it occurs to the entire range, so the entire range of values will need up be updated, with their corresponding price changes.

The second worksheet is the price per Kg.

This is slightly harder, where the "Contract Price" is the price the customer pays per Kg of product.

If there is a price increase of £2 per Kg across the range, then £2 will need to be added to each of the "Contract Prices", but this will also need to be allocated to the value in the drop down box at "B6".

I hope this makes sense, and that someone will be able to help me out!

Cheers,

Nick

[watersev]

hi, NickPDC, though I had no much to finish that to the end I hope this is a good start in the right direction. Check attachment, run code "test". If everything is OK with sheet 1 we shall move on to sheet 2

[NickPDC]

hi, NickPDC, though I had no much to finish that to the end I hope this is a good start in the right direction. Check attachment, run code "test". If everything is OK with sheet 1 we shall move on to sheet 2

Hi Watersev,

Thanks for the help!

Some works and some doesnt!

The "Contract Price" doesnt seem to update 10%, 20% or whatever the Percentage is set at.

Also, the increase in percentage doesnt paste into the column relating to the drop down. It selects it, but no values are pasted!

Thank you very much for your help

Nick

[NickPDC]

Now I see what is happening! :D

You are taking the percentage from the column corresponding with the drop down.
So if the machining has 0 all the way down, it appears to do nothing as 10% of 0 is 0.

This is a percentage of the "Contract Price", and the 10% being added on, needs to be pasted into the column relating to the drop down.

I hope that makes sense!

Thanks! Nick

[NickPDC]

Still needing some help with this!

I have tried looking through and modifying the code watersev has written, but without any progress at the moment!

Thanks,

Nick

[watersev]

hi, Nick, please check attachment, run code "test" still dealing with the first sheet only. The code does the following:
- finds and updates values for dropdown selected column by percentage of contract price
- updates effective date
If I'm wrong, it would be nice to get exact example (shown in file) what and when should happen with what data.

[NickPDC]

hi, Nick, please check attachment, run code "test" still dealing with the first sheet only. The code does the following:
- finds and updates values for dropdown selected column by percentage of contract price
- updates effective date
If I'm wrong, it would be nice to get exact example (shown in file) what and when should happen with what data.

Watersev,
Thats perfect! Works a charm.
The Contract price needs to update by 10% as well and then the first sheet is done.

Thank you!

Nick

[watersev]

Nick, what should come first Contract price increase and then selected column, right?

[NickPDC]

Selected column, and then contract price!
As the selected column is showing the price increase :D

Cheers!

[watersev]

please check attachment, finalizing sheet 1

[NickPDC]

please check attachment, finalizing sheet 1

Absolutely perfect thank you very much!

[watersev]

can you give a bit more detailed explanation for sheet 2 because I do not think I get it completely

[NickPDC]

can you give a bit more detailed explanation for sheet 2 because I do not think I get it completely

Right, sheet 2 is slightly more confusing.

But some of the code can be reused, as we are still needing to use the drop down list for the column allocation. And the "New Effective Date" needs to copy over as well.

Basically, data will be added into the "Increase" field, in the form of 0.02, 0.04 etc.

This will need to multiply by the "Unit Weight" for each field. The SUM of that equals the value that gets allocated to a column.

All of the columns then need to add up to make the "Contract Price" for each indivudual row.

Hope that makes sense!

Cheers

Nick

[watersev]

please check attachment, run code "test1', the logic is the same, updating selected column from the drop-down list, increasing Unit Weight and updating Effective date

[NickPDC]

please check attachment, run code "test1', the logic is the same, updating selected column from the drop-down list, increasing Unit Weight and updating Effective date

Thanks so much for the assistance! I appreciate it

Very very close!

The number in the "Unit Weight" does not need to change.

And the columns "R-V" need to add to the"Contract Price".

Then it will be finished!

Thanks!

Nick

[watersev]

so we multiply Unit weight by Increase value, add Contract price and put total value into selected column. Also we are updating effective date. Right?

[NickPDC]

Effective date is being updated.

Multiply Unit weight by increase value and put that into the allocated column (Selected from drop down list).
The Contract price is then columns R-V added onto its value.

I hope I am making sense!

Thanks!

[watersev]

please check attachment, run code "test1"

[NickPDC]

please check attachment, run code "test1"

That is it!
Perfect.
Absolutely spot on.

I cannot thank you enough Watersev!!

I have left you some Rep.

Thanks again,

Nick