Quick Macro Question for Sales Spreadsheet

[SkzDaLimit]

Another quick mystery to solve. I tried modifying a macro from a previous spreadsheet you all helped me on to try and make this work.

G23 contains the total amount of money received for an item.
I23 is a drop down with 3 different payment options: PayPal, Amazon Payment and Google Checkout.

PayPal and Google use the same formula to compute the final fee in J23: =IF($G23>0,$G23*2.9%+0.3)

Amazon uses a 2 tier system thus the formula: =IF($G23>10,$G23*2.9%+0.3,IF($G23<=10,$G23*5%+0.05))

This is the macro I came up with:

Private Sub Worksheet_Change(ByVal Target As Range)
' Michael Alan Johnson, 10/04/2011
If Intersect(Target, Range("I23:I1000")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target = "" Then Range("J" & Target.Row) = 0
With Application
.EnableEvents = False
.ScreenUpdating = False
Select Case Target.Value
Case "PayPal"
'=IF($G23>0,$G23*2.9%+0.3)
'=IF(G23>0,G23*2.9%+0.3)
Range("J" & Target.Row).FormulaR1C1 = "=IF($G23>0,$G23*2.9%+0.3)"
Case "Amazon Payment"
'=IF($G23>10,$G23*2.9%+0.3,IF($G23<=10,$G23*5%+0.05))
'=IF(G23>10,G23*2.9%+0.3,IF(G23<=10,G23*5%+0.05))
Range("J" & Target.Row).FormulaR1C1 = "=IF($G23>10,$G23*2.9%+0.3,IF($G23<=10,$G23*5%+0.05))"
Case "Google Checkout"
'=IF($G23>0,$G23*2.9%+0.3)
'=IF(G23>0,G23*2.9%+0.3)
Range("J" & Target.Row).FormulaR1C1 = "=IF($G23>0,$G23*2.9%+0.3)"
End Select
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub

Can't seem to get it to work.......

Excel Workbook
	G	I	J
21	Total Received	Checkout Method	Checkout Fee
22
23	$12.00
...

Excel 2003

 

[SkzDaLimit]

Bumpity bump..... Thank you.

 

[Sal Paradise]

Question 1:
If you are using a macro, why bother putting formulas in the cells (why not just put the value?)

Question 2:
If you are using formulas, why not just use the "List" feature of excel to auto-add the formula whenever a new row is added?

 

[SkzDaLimit]

Question 1:
If you are using a macro, why bother putting formulas in the cells (why not just put the value?)

Question 2:
If you are using formulas, why not just use the "List" feature of excel to auto-add the formula whenever a new row is added?

Q1. The purpose of the macro is that is calculates the value automatically based on known factors.

Q2. Not that familiar enough with Excel to figure it out. The macro I modified is from a other spreadsheet I have that works just fine. Not sure why it won't work now.

Can someone help out? Thanks again.

 

[Sal Paradise]

For instance, instead of this line:

Range("J" & Target.Row).FormulaR1C1 = "=IF($G23>0,$G23*2.9%+0.3)

You could use this line:

Range("J" & Target.Row).Value = Range("G" & Target.Row).Value * 2.9% + 0.3

It won't be a formula, but it will give your answer.

If you want to be more flexible, then there are a million ways to do it, but the way you're doing it, you're putting the same formula (for row 23) in the cells, rather than the appropriate row number.

 

[SkzDaLimit]

For instance, instead of this line:

Range("J" & Target.Row).FormulaR1C1 = "=IF($G23>0,$G23*2.9%+0.3)

You could use this line:

Range("J" & Target.Row).Value = Range("G" & Target.Row).Value * 2.9% + 0.3

It won't be a formula, but it will give your answer.

If you want to be more flexible, then there are a million ways to do it, but the way you're doing it, you're putting the same formula (for row 23) in the cells, rather than the appropriate row number.

I gave that a shot and still couldn't get it to work. What's the easiest way to accomplish what I am trying to do with the least amount of headaches?

 

[Sal Paradise]

Assuming you're in Excel 2007, I would make a table like this:

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">21</td><td style="font-weight: bold;;">Total Received</td><td style="font-weight: bold;;">FillerA</td><td style="font-weight: bold;;">Checkout Method</td><td style="font-weight: bold;;">FillerB</td><td style="font-weight: bold;;">Checkout Fee</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;;"> $ 454.18 </td><td style="text-align: right;;">0</td><td style=";">Google Checkout</td><td style="text-align: right;;">0</td><td style="text-align: right;;"> $ 25.77 </td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;;"> $ 878.16 </td><td style="text-align: right;;">0</td><td style=";">PayPal</td><td style="text-align: right;;">0</td><td style="text-align: right;;"> $ 21.96 </td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: right;;"> $ 746.90 </td><td style="text-align: right;;">0</td><td style=";">Amazon Payment</td><td style="text-align: right;;">0</td><td style="text-align: right;;"> $ 15.07 </td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: right;;"> $ 509.31 </td><td style="text-align: right;;">0</td><td style=";">PayPal</td><td style="text-align: right;;">0</td><td style="text-align: right;;"> $ 21.79 </td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: right;;"> $ 740.97 </td><td style="text-align: right;;">0</td><td style=";">PayPal</td><td style="text-align: right;;">0</td><td style="text-align: right;;"> $ 2.15 </td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: right;;"> $ 63.76 </td><td style="text-align: right;;">0</td><td style=";">PayPal</td><td style="text-align: right;;">0</td><td style="text-align: right;;"> $ 20.76 </td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: right;;"> $ 705.49 </td><td style="text-align: right;;">0</td><td style=";">PayPal</td><td style="text-align: right;;">0</td><td style="text-align: right;;"> $ 13.02 </td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: right;;"> $ 438.69 </td><td style="text-align: right;;">0</td><td style=";">Google Checkout</td><td style="text-align: right;;">0</td><td style="text-align: right;;"> $ 20.85 </td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: right;;"> $ 708.78 </td><td style="text-align: right;;">0</td><td style=";">Google Checkout</td><td style="text-align: right;;">0</td><td style="text-align: right;;"> $ 5.02 </td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: right;;"> $ 162.63 </td><td style="text-align: right;;">0</td><td style=";">Amazon Payment</td><td style="text-align: right;;">0</td><td style="text-align: right;;"> $ 28.20 </td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: right;;"> $ 962.16 </td><td style="text-align: right;;">0</td><td style=";">Amazon Payment</td><td style="text-align: right;;">0</td><td style="text-align: right;;"> $ 10.66 </td></tr><tr ><td style="color: #161120;text-align: center;">33</td><td style="text-align: right;;"> $ 357.09 </td><td style="text-align: right;;">0</td><td style=";">PayPal</td><td style="text-align: right;;">0</td><td style="text-align: right;;"> $ 7.61 </td></tr><tr ><td style="color: #161120;text-align: center;">34</td><td style="text-align: right;;"> $ 252.14 </td><td style="text-align: right;;">0</td><td style=";">PayPal</td><td style="text-align: right;;">0</td><td style="text-align: right;;"> $ 0.30 </td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K22</th><td style="text-align:left">=ROUND(<font color="Blue">IF(<font color="Red">AND(<font color="Green">I22="Amazon Payment",G22<=10</font>),G23*5%+0.05,G23*2.9%+0.3</font>),2</font>)</td></tr></tbody></table></td></tr></table><br />

And then make a table by following the instructions here:
http://www.techrepublic.com/blog/msoffice/use-excel-tables-for-quick-formula-auto-fill/4882

Otherwise, I would make a macro more like this:
http://www.ozgrid.com/forum/showthread.php?t=49870

But it depends on how often the formulas may change, or how you work with your formats, and how long you want this to work for.

 

[SkzDaLimit]

Using Excel 2003 and I am more confused than ever now. Sorry.

 

[Sal Paradise]

I tried giving you code to add the value, and tried giving you other ways to look at it. I am out of ideas on how to help you unless you can communicate more clearly what your problem is, what you expect to happen, what is actually happening, and what sort of solution you are looking for.

 

[SkzDaLimit]

I tried giving you code to add the value, and tried giving you other ways to look at it. I am out of ideas on how to help you unless you can communicate more clearly what your problem is, what you expect to happen, what is actually happening, and what sort of solution you are looking for.

I thought I had made it pretty clear in the first post.

I have a sales spreadsheet.

As shown in the little graphic of my first post, Column G is filled with the amount of money a person has sold an item for.

Column I (though it didn't show in the graphic) is a drop down with 3 methods a person used to make a payment: Amazon Payment, Google Checkout and PayPal.

I modified a macro that I had used on another spreadsheet to use on this new one that allows me, for example, to make an entry of $12.00 in G23 and then choose one of the payment methods in Column I. The macro then calculates the fee according to the following formulas below and inserts that value into J23:

=IF(G23>0,G23*2.9%+0.3) (used for PayPal and Google)

=IF(G23>10,G23*2.9%+0.3,IF(G23<=10,G23*5%+0.05)) (used by Amazon)

The macro that I modified didn't work. Don't know why. All I want is the macro to work. No lists, no tables. Just a macro.

I even tried writing a formula to make it work but got the dreaded "too many arguments for this function" error. Perhaps someone could look at that too and make a suggestion:

=IF($I23=PayPal,$G23>0,$G23*2.9%+0.3,IF($I23=Amazon Payment,$G23>10,$G23*2.9%+0.3,IF($G23<=10,$G23*5%+0.05,IF($I23=Google Checkout,$G23>0,$G23*2.9%+0.3))))