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
Last edited by aarons; 03-08-2010 at 08:07 PM. Reason: solved
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.
teylyn
Microsoft MVP - Excel
At Excelforum, you can say "Thank you!" by clicking theicon below the post.
Avoid pie charts with more than two data points. Why? See here (pdf, 559 kb). The only acceptable pie chart is here.
Why a macro? A simple lookup table will do this job in a formula.
With the table in columns D and E, put this formula in B2 then copy down:Code:A B C D E Cost Retail vCost vRetail 1 1.50 9.99 0 9.99 2 12.50 31.99 4 12.99 3 5 17.99 4 7 22.99 5 9 27.99 6 12 31.99 7 14 35.99 8
=VLOOKUP($A2, $D$2:$E$100, 2, FALSE)
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Sorry I am not too familiar with formulas as my background is more in coding. Help is very much appreciated.Originally Posted by JBeaucaire
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!!
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
teylyn
Microsoft MVP - Excel
At Excelforum, you can say "Thank you!" by clicking the
LOL thanks so much I just figured that out as you posted thanks so so much Teylyn you both are awesome for the help!!!
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.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks