Thread: Help with applying paypal fee to a row

Hello ebbo,

The following macro has been added to your button in the attached workbook.

Sub ApplyPayPalFees()

  Dim Cell As Range
  Dim Price As Currency
  Dim Rng As Range
  Dim RngEnd As Range
  Dim ServiceFee As Double
  Dim TransactionFee As Double
  Dim VAT As Currency
  Dim Wks As Worksheet
  
    Set Wks = ActiveSheet
    
    Set Rng = Wks.Range("A2")
    Set RngEnd = Wks.Cells(Rows.Count, Rng.Column).End(xlUp)
    If RngEnd.Row < Rng.Row Then Exit Sub Else Set Rng = Wks.Range(Rng, RngEnd)
    
      ServiceFee = (1 - Wks.Range("I2"))
      TransactionFee = Wks.Range("J2")
      
      For Each Cell In Rng
        Price = Cell * ServiceFee
        VAT = Cell.Offset(0, 1) * ServiceFee
        Cell = Price
        Cell.Offset(0, 1) = VAT
        Cell.Offset(0, 2) = (Price + VAT) - TransactionFee
      Next Cell
      
End Sub

Hello ebbo,

I made the changes to the macro. This will let you apply the PayPal and service charges to the selected cells in columns "A:C". If you don't select all three columns, you will be told to do so. You can also select more than a single row at time.

'Thread:  http://www.excelforum.com/excel-programming/780300-help-with-applying-paypal-fee-to-a-row.html#post2553889
'Poster:  Ebbo
'Written: June 16, 2011
'Updated: June 30, 2011 - Added error handling and manual selection of data.
'Author:  Leith Ross

Sub ApplyPayPalFees()

  Dim Price As Currency
  Dim Row As Range
  Dim Rng As Range
  Dim ServiceFee As Double
  Dim TransactionFee As Double
  Dim VAT As Currency
  Dim Wks As Worksheet
  
    Set Wks = ActiveSheet
    
    Set Rng = Wks.Range("A1").CurrentRegion
    Set Rng = Intersect(Rng, Rng.Offset(1, 0))
    
    If Intersect(Selection, Rng) Is Nothing Then Exit Sub
    
    If Selection.Cells.Count < Rng.Rows(1).Cells.Count Then
       MsgBox "Please select all cells in the row.", vbExclamation
       Exit Sub
    End If
    
      ServiceFee = (1 - Wks.Range("I2"))
      TransactionFee = Wks.Range("J2")
      
      For Each Row In Selection.Rows
        Price = Row.Cells(1, 1) * ServiceFee
        VAT = Row.Cells(1, 2) * ServiceFee
        Row.Cells(1, 1) = Price
        Row.Cells(1, 2) = VAT
        Row.Cells(1, 3) = (Price + VAT) - TransactionFee
      Next Row
      
End Sub