|
quick question
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 |
quick question
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 "Cammy" ... 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 |
quick question
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" wrote: 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 "Cammy" ... 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 |
quick question
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 "Cammy" ... 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" wrote: 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 "Cammy" ... 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 |
quick question
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 "Cammy" wrote: 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" wrote: 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 "Cammy" ... 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 |
quick question
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 "Cammy" ... 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 "Cammy" wrote: 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" wrote: 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 "Cammy" ... 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 |
| All times are GMT +1. The time now is 10:40 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com