VBA tweak to include a second IF statement

[chappy]

Hi I would like to tweak some code that works well to add one additional IF statement. The code I am using is below. I would like to build in the following formula:

IF(appliedCommission >= 0.35, appliedCommission, 0.35) + (prEntry * Quantity * transactFee)

Any assistance would be most appreciated!

'Vlook to calculate the commission payable'
Private Sub calculateCommissionPayable(actvSheet As Worksheet)
Dim rw As Range
Dim col As Range
Dim commPS As Double
Dim transactFee As Double
Dim quantity As Integer 'Current Quantity
Dim prEntry As Integer 'Share price

Set instructSheet = ActiveWorkbook.Sheets("DAS_DATA")
Set instructSheet = ActiveWorkbook.Sheets("INSTRUCTIONS")

commPS = instructSheet.Range("E47")
transactFee = instructSheet.Range("E49")

For Each rw In actvSheet.UsedRange.Rows 'Looping on each row of Sheet1'

If rw.row <> 1 Then 'Skip first header row'

Dim appliedCommission As Double 'Calculated commission'

quantity = convertToInt(Cells(rw.row, 5).Value)

appliedCommission = quantity * commPS 'references the named range "COMM_PER_SHARE" on "INSTRUCTIONS" sheet'

actvSheet.Cells(rw.row, 14).Value = appliedCommission 'applies commission per ticket

End If 'Check if not First Row'

Next rw
Exit Sub
End Sub

 

[Snakehips]

@chappy Can you clarify what you are wanting?
Having computed appliedCommission, you want to ensure it is a minimum of 0.35 ???
Then something, ????? = appliedCommission + prEntry * Quantity * transactFee

 

[chappy]

@chappy Can you clarify what you are wanting?
Having computed appliedCommission, you want to ensure it is a minimum of 0.35 ???
Then something, ????? = appliedCommission + prEntry * Quantity * transactFee

Yes I am very sorry for not being clear. I am trying to ensure that a minimum cost is applied. Currently the code calculates commission payable based on the number of shares traded (quantity) multiplied by the commission per share (CommPS). However, this may calculate a value lower than the real minimum commission which is 35 cents each way, i.e. 70 cents in total.

I would like to ensure that if the result of Quantity multiplied by CommPS is less than 70 cents, then use 70 cents otherwise use quantity multiplied by CommPS.

I will drop the additional transaction fee for the moment that I mentioned initially "+ (prEntry * Quantity * transactFee)" so please ignore than.

 

[Snakehips]

Then just the one additional line should do it...

'Vlook to calculate the commission payable'
Private Sub calculateCommissionPayable(actvSheet As Worksheet)
Dim rw As Range
Dim col As Range
Dim commPS As Double
Dim transactFee As Double
Dim quantity As Integer 'Current Quantity
Dim prEntry As Integer 'Share price

Set instructSheet = ActiveWorkbook.Sheets("DAS_DATA")
Set instructSheet = ActiveWorkbook.Sheets("INSTRUCTIONS")

commPS = instructSheet.Range("E47")
transactFee = instructSheet.Range("E49")

For Each rw In actvSheet.UsedRange.Rows 'Looping on each row of Sheet1'

If rw.Row <> 1 Then 'Skip first header row'

Dim appliedCommission As Double 'Calculated commission'

quantity = convertToInt(Cells(rw.Row, 5).Value)

appliedCommission = quantity * commPS 'references the named range "COMM_PER_SHARE" on "INSTRUCTIONS" sheet'
    
    '********
    If appliedCommission < 0.7 Then appliedCommission = 0.7     '**** ensure appliedCommission  minimum 0.70
    '********
    
actvSheet.Cells(rw.Row, 14).Value = appliedCommission 'applies commission per ticket

End If 'Check if not First Row'

Next rw
Exit Sub
End Sub

 

[chappy]

Then just the one additional line should do it...

'Vlook to calculate the commission payable'
Private Sub calculateCommissionPayable(actvSheet As Worksheet)
Dim rw As Range
Dim col As Range
Dim commPS As Double
Dim transactFee As Double
Dim quantity As Integer 'Current Quantity
Dim prEntry As Integer 'Share price

Set instructSheet = ActiveWorkbook.Sheets("DAS_DATA")
Set instructSheet = ActiveWorkbook.Sheets("INSTRUCTIONS")

commPS = instructSheet.Range("E47")
transactFee = instructSheet.Range("E49")

For Each rw In actvSheet.UsedRange.Rows 'Looping on each row of Sheet1'

If rw.Row <> 1 Then 'Skip first header row'

Dim appliedCommission As Double 'Calculated commission'

quantity = convertToInt(Cells(rw.Row, 5).Value)

appliedCommission = quantity * commPS 'references the named range "COMM_PER_SHARE" on "INSTRUCTIONS" sheet'
   
    '********
    If appliedCommission < 0.7 Then appliedCommission = 0.7     '**** ensure appliedCommission  minimum 0.70
    '********
   
actvSheet.Cells(rw.Row, 14).Value = appliedCommission 'applies commission per ticket

End If 'Check if not First Row'

Next rw
Exit Sub
End Sub

Thank you very much Snakehips it works perfectly!