Modifying a cell via macro.

[Ted-Ebay]

Folks,
I am having one heck of a time getting a macro to work and need ya'lls help.

I am fairly familiar with Excel, but not an expert.

I am attempting to run a macro on a radio/option button, that will check multiple check-boxes and set values in multiple cells.

Example: User wants to add a package deal. User clicks the radio button for the package they want and they get 7 items at 1/2 price, and they also get 4 items at a set base value and the option to choose one of two items at a set base value.

1. The macro should check the 7 check-boxes for the items that are at 1/2 cost, so that the user can see which items are at half cost when buying multiples of that item and/or multiple items. (Does not work)

2. The Macro should set item A, B C, and D at a specific value. (WORKS)

3. The macro should pop up a choice of item E, or item F, and wait for the user to choose and set that item at a base value. (WORKS)

Here is what I have done so far:

I have a set of cells that show up to 3 areas that the customer can use to get their items. Some items are available from all 3 sources, some not. In the following example, the item is available from one source. And there is the possibility of additional items from a package deal that the customer does not have to buy. A promotional item source.

The F2 cell formula =IF(SUM(G2)+(J2)>50,SUM((G2)+(J2)-50)/2+50,SUM(G2)+(J2))

G column is the primary source, H column is the secondary source, I column is the tertiary source, and J column is the promotional source; that is free.

Item cost is normal up to 50 items (regardless of source), then double cost after 50 items.

This works just fine in the total items bought cell: F2

The packages also can have stuff on sale. Using a radio/option button for packages and only one package is select-able at a time.
So I have a macro for those packages that allow the customer to see items that are 1/2 cost and 1/4 cost, and to set promotional items as well.

In this instance, item CRM is at 1/2 cost and has a promotional source of 20 free items.(As part of a package.)

So the macro sets the promotional items value at 20(cell J2), and allows the user to buy more from the primary source (cell G2).

The base formula above needs to account for there being a possibility of the items being 1/2 cost or 1/4 cost due to a package.

I tried to use a nested IF statement that had the above formula in it for IF this, and again for IF that, but modified to affect the cost.

It failed due to too many functions.

I have the Option button macro for the chosen package deal; that resets all promotional items to zero, and then sets the promotional value in 6 cells, and to activate the check-box to show the customer the 1/2 cost for 1 item.

Sub OptionButton540_Click()
Range("J2:J58").Value = "0"
Range("J2").Value = (Range("J2").Value) + 20
Range("J7").Value = (Range("J7").Value) + 20
Range("J8").Value = (Range("J8").Value) + 20
Range("J9").Value = (Range("J9").Value) + 20
Range("J58").Value = (Range("J58").Value) + 40
Range("J56").Value = (Range("J56").Value) + 20
ActiveSheet.CheckBoxes("Check Box 541").Value = xlOn
End Sub

This also works fine.

The problem I am having is being able to either change the formula for cell F2, to reflect the 1/2 cost of the item; or to add a function into the macro that changes the value of either F2 or G2.

Sorry if I am being scattered with this.

Any ideas?

[AlphaFrog]

It's very hard to follow. It seems your are describing several problems and asking more than one question. Perhaps you could break it down to one specific question. Then either follow up with another related question, or start another thread. Also, attaching an example workbook would go a long way to understanding what you are doing.

Here's another way to write your formula though I really don't understand what you are asking.

=MIN(50,(G2+J2)) + MAX(0, (G2+J2-50)/2)

[Ted-Ebay]

Was trying to figure out a way to change the formula to reflect the package deals.

Used this:
Worksheets("Generator").Range("F2").Formula = "=IF(SUM(G2*2)+(J2)>50,SUM((G2*2)+(J2)-50)/2+50,SUM(G2*2)+(J2))"
This allowed me to change the formula for the items totaling to reflect the 1/2 cost.

Solved it myself.

Thanks for the reply.

[AlphaFrog]

You could shorten the formula to this...
Worksheets("Generator").Range("F2").Formula = "=IF(G2*2+J2>50,(G2*2+J2-50)/2+50,G2*2+J2)"

Or this...
Worksheets("Generator").Range("F2").Formula = "=MIN(50,G2*2+J2)+MAX(0, (G2*2+J2-50)/2)"