quick question

[Guest]

I have this macro (see below) which calculates commissions and fees using an
lot of If functions. Unfortunately the function seems to post a numberinto
the output columns a number even if the ticker (eg FLGH7) has not been
programmed into the macro. Is it possible to add to the macro some coding
which means the output will print 'ERROR' if the ticker in columns D is not
recognised?



Dim i As Integer
Dim j As Integer
Dim NumberOfTrades As Integer
Dim Fees As String
Dim Commissions As String

Range("M1") = "Clearing and Exchange Fees"
Range("N1") = "Execution Commissions"

NumberOfTrades = Application.CountA(Columns(4)) - 1


For j = 1 To NumberOfTrades

If Range("D1").Offset(j, 0) = "FLGH7" Then
Fees = "1.20"
Commissions = "0.55"
End If

If Range("D1").Offset(j, 0) = "FLGM7" Then
Fees = "0.98"
Commissions = "0.54"
End If


Volume = Range("F1").Offset(j, 0)

Range("D1").Offset(j, 9).Value = Fees * Volume
Range("D1").Offset(j, 10).Value = Commissions * Volume

If Range("D1").Offset(j, 0) = "FLGU7" Then
Fees = "0.96"
Commissions = "0.53"
End If

Next j

Columns("M:M").EntireColumn.AutoFit
Columns("N:N").EntireColumn.AutoFit

End Sub

 

[NickHK]

What about a Select Case instead :

Const ERR_MSG As String="Error - No match"

Select Case Range("D1").Offset(j, 0)
Case "FLGH7"
Fees = "1.20"
Commissions = "0.55"
Case "FLGM7"
Fees = "0.98"
Commissions = ...etc
'...........
Case Else 'no match
Fees = ERR_MSG
Commissions = ERR_MSG
End Select

NickHK

 

[Guest]

yes this does work but unfortunately it then gets stuck on the later
calculation part when it does
Range("D1").Offset(j, 9).Value = Fees * Volume
Range("D1").Offset(j, 10).Value = Commissions * Volume

since ERR_MSG cannot be used in this calculation, any more tips on how to
avoid this problem?

 

[NickHK]

If you are calculating with those variables, they should not be dimmed as
strings, they should be singles, or Variants.

What value do want in those 2 cells then, if Fees and Commissions are not
defined ?

You can use IsNumeric(Fees) to test if you have a valid number or an error
message.

NickHK

 

[Guest]

I have fixed it now so it doens't get stuck on the error message...
Thanks Nick

Instead I put

Case Else 'no match
Fees = 0
Commissions = 0
End Select

Volume = Range("F1").Offset(j, 0)

If Fees = 0 Then
Range("D1").Offset(j, 9).Value = ERR_MSG
Else: Range("D1").Offset(j, 9).Value = Fees * Volume
End If

If Commissions = 0 Then
Range("D1").Offset(j, 10).Value = ERR_MSG
Else
Range("D1").Offset(j, 10).Value = Commissions * Volume
End If

 

[NickHK]

OK, but do you need to test both Fees and Commissions ?
Surely, if Fees=0 then Commissions =0 also ?

If Fees = 0 Then
Range("D1").Offset(j, 9).Value = ERR_MSG
Range("D1").Offset(j, 10).Value = ERR_MSG
Else
Range("D1").Offset(j, 9).Value = Fees * Volume
Range("D1").Offset(j, 10).Value = Commissions * Volume
End If

Also, I assume you will never have a case where you do not charge someone a
Fee or Commission for a transaction. Thus Fee and/or Commission could
actually be 0, but your code would treat it as an error.
You could use -1 instead, to allow for that possibility.

NickHK