Excel 2007 : excel if then macro help

[aarons]

I am wondering if there is an If then macro that can be used to convert a number in one cell to a predefined set number in another cell.
I am working on a retail cost spreadsheet for a point of sales database.
I have a pricing formula that goes:
$2.00-$3.99 cost will be $9.99 markup
$4.00-$4.99 cost will be $12.99 markup
$5.00-$6.99 cost will be $17.99 markup
and so on all the way to $200 cost.

What I would like to do is put the actual cost in one cell and have a predefined If then with my pricing formula to set the retail markup in another adjacent cell.

For example:
saying C is cell A2-A1000 for example
and R is cell B2-B21000

cost = C
retail = R

If C =< 3.99
Then R = 9.99
If C =< 4.99
Then R = 12.99

etc etc.

I don't know how in excel to associate cells into VBA any help on how to set this up or help would be great

[teylyn]

Hi,

is there a particular reason you want to do this with VBA instead of a worksheet formula?

You can set up your markup structure in a part of the workbook and then use a Lookup formula to lookup the correct markup based on the data in column A.

Heck of a lot easier to maintain than a macro.

[JBeaucaire]

Why a macro? A simple lookup table will do this job in a formula.

Please Login or Register  to view this content.

With the table in columns D and E, put this formula in B2 then copy down:

=VLOOKUP($A2, $D$2:$E$100, 2, FALSE)

[aarons]

Why a macro? A simple lookup table will do this job in a formula.

Please Login or Register  to view this content.

With the table in columns D and E, put this formula in B2 then copy down:

=VLOOKUP($A2, $D$2:$E$100, 2, FALSE)

Sorry I am not too familiar with formulas as my background is more in coding. Help is very much appreciated.

The problem I have when I set it up as the example above is:

I have a cost of 7.49 the lookup seems to only lookup the defined number in the vcode column.
It works great if the cost is 7.00 but not if it is 7.01-7.99.
That is why I thought Macro instead since my costs can range greatly.

Since an If/Then can easily grasp the range with an (=)equal to or (<) less than as my example I posted above.
Is there a way to utilize the VLOOKUP to read a RANGE such as my cost variants from for example 7.01 to 7.99?

In your example above you used 1.50 as an example cost when I did a simple test using the table you posted above it gives me a #NA using 0, 4, 5 etc as you used above.

Thanks for the help so far I realy appreciate it. Any continued help would be awesome!!

[teylyn]

You need to change the last argument of the VLookup to TRUE


=VLOOKUP($A2,$D$2:$E$100,2,TRUE)

then the formula will return the next match that is smaller than or equal to the lookup value

hth

[aarons]

You need to change the last argument of the VLookup to TRUE


=VLOOKUP($A2,$D$2:$E$100,2,TRUE)

then the formula will return the next match that is smaller than or equal to the lookup value

hth

LOL thanks so much I just figured that out as you posted thanks so so much Teylyn you both are awesome for the help!!!

[JBeaucaire]

My bad....how embarrassing is that? (bang head against wall here)

========
If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.