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]

Bump.

 

[Sal Paradise]

Okay, again, I will state, you can use code like this to avoid having to put a formula in the cells:

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

If you want to add a formula, the formula I provided would work fine:

=ROUND(IF(AND(I22="Amazon Payment",G22<=10),G23*5%+0.05,G23*2.9%+0.3),2)

The point is that you have said that neither of these solutions work for you. So where are you stuck? What is wrong with my formulas? What is wrong with:

Private Sub Worksheet_Change(ByVal Target As Range)
    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
        Range("J" & Target.Row).FormulaR1C1 = "=ROUND(IF(AND(I" & Target.Row & "=""Amazon Payment"",G" & Target.Row & "<=10),G" & Target.Row & "*5%+0.05,G" & Target.Row &"*2.9%+0.3),2)
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub

 

[SkzDaLimit]

My apologies, nothing wrong with your suggestions at all. As I have stated, I have a difficult time grasping some of the more complicated things about Excel. I guess I am one of those folks you have to hold my hand and talk me through everything.

Anyways, I tried the last suggestion:

Private Sub Worksheet_Change(ByVal Target As Range)     
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         
Range("J" & Target.Row).FormulaR1C1 = "=ROUND(IF(AND(I" & Target.Row & "=""Amazon Payment"",G" & Target.Row & "<=10),G" & Target.Row & "*5%+0.05,G" & Target.Row &"*2.9%+0.3),2)       .EnableEvents = True         
.ScreenUpdating = True     
End With End Sub

Got a #NAME? error. Thanks again.

 

[Sal Paradise]

What does the formula in the cell say? Is it correct? If not, where's the error?

I understand that you want to be led through step by step, but you need to put some effort into finding where the problem is yourself...

 

[SkzDaLimit]

What does the formula in the cell say? Is it correct? If not, where's the error?

I understand that you want to be led through step by step, but you need to put some effort into finding where the problem is yourself...

Sorry, should have put the formula in there:

=ROUND(IF(AND('I23'="Amazon Payment",'G23'<=10),'G23'*5%+0.05,'G23'*2.9%+0.3),2)

The #NAME? error appears in J23 where the fee should have gone.

 

[Michael M]

You don't need to put the formula in anywhere.
Simply copy the code correctly and paste it into the Sheet module.
I note that your last response has the code pasted incorrectly.
Try this

Private Sub Worksheet_Change(ByVal Target As Range)
    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
        .Range("J" & Target.Row).Formula = "=ROUND(IF(AND(I" & Target.Row & "=""Amazon Payment"",G" & Target.Row & "<=10),G" & Target.Row & "*5%+0.05,G" & Target.Row & "*2.9%+0.3),2)"
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub

 

[SkzDaLimit]

You don't need to put the formula in anywhere.
Simply copy the code correctly and paste it into the Sheet module.
I note that your last response has the code pasted incorrectly.
Try this

Private Sub Worksheet_Change(ByVal Target As Range)
    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
        .Range("J" & Target.Row).Formula = "=ROUND(IF(AND(I" & Target.Row & "=""Amazon Payment"",G" & Target.Row & "<=10),G" & Target.Row & "*5%+0.05,G" & Target.Row & "*2.9%+0.3),2)"
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub

Worked liked a charm and many many thanks!

 

[Michael M]

Glad you managed to get it sorted thanks for the feedback....Sal did all the hard yards !!
I think the final problem was here

Range("J" & Target.Row).FormulaR1C1   'this shouldn't be R1C1

 

[SkzDaLimit]

Glad you managed to get it sorted thanks for the feedback....Sal did all the hard yards !!
I think the final problem was here

Range("J" & Target.Row).FormulaR1C1   'this shouldn't be R1C1

Many thanks to Sal as well. I did learn a few things so all is good....

 

[Sal Paradise]

Glad it worked out for you.