Macro Help Needed

[mhoy06]

Hello,

I am supposed to come up with a macro that will do the following:

There are two columns: List Price and Our Price.

The macro is supposed to be able to increment List Price until it is at least 15% higher than Our Price. (That way when we import it onto the site it shows that the customer is saving at least 15%)

To make things worse it has perform this calculation on every row where this is data. Some spreadsheets only have 1 product while others have 500 or more.

Any help is appreciated.

So far I've only been able to do the following (and this isn't what I need really):

Option Explicit
Sub Savings1()

Dim Savings As Single
Dim map_price As Single
Dim list_price As Single
map_price = Range("B2").Value
list_price = Range("A2").Value
Savings = 1 - (map_price / list_price)
Range("C2").Select
ActiveCell.FormulaR1C1 = Savings
Selection.NumberFormat = "0%"

End Sub

Thanks,

Mike

 

[Gavona]

I'm not very comfortable with the concept of adjusting the list price rather than adjusting your price!

You need to re-think your specification

increment List Price until it is at least 15% higher than Our Price

If your price is 85 then this statement says you want to increment it by (at least) 15% which would give you a (minimum) list price of 97.75.
However 12.75 (97.95 - 85) is only 13% less than 97.75 so the customer would not be saving at least 15%.

I think that you actually want the new list price to equal the larger of:
i) Current List Price
ii) Our Price x 100 / 85

So a formula of =max(listprice,ourprice*100/85) would do the trick.

Gavin

 

[Gavona]

To make things worse it has perform this calculation on every row where this is data. Some spreadsheets only have 1 product while others have 500 or more.

How are you going to select the cells where you need this formula? Once you have resolved that then something like:

With selection
' adjust following line to be equivalent to "=MAX(ListPrice,OurPrice*100/85)" 
  .FormulaR1C1 = "=MAX(RC[-1],RC[-2]*100/85)"
  .Value = .value
End with

This converts the formula to a value


Gavin

 

[SkipVought]

Some spreadsheets only have 1 product while others have 500 or more.

This is your second problem. By chopping your data up into separate sheets you have...
[tt]
1. bypassed the data analyis features of Excel and

2. made you job at least 10 times more difficult that it need to be.
[/tt]
Similar data ought to be in a single table. By adding one additional column to identify PRODUCT, and combining the data from all your sheets into one, you will greatly enhance your workbook and similarly decrease the effort it will take to analyze and maintain your data.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}