Build A Formula
Sometimes it seems that no matter how hard you try, somebody wants more. In this case, we needed a formula to be placed in a cell when the spreadsheet was created so that if sales figures were tweaked, the commission would change.Most of the salesmen’s names were in the system like AB, RD, TS, etc. Occasionally 2 salesmen would split a commission and I had to work out a scheme that my program could always recognize. The syntax is 2 initials, the first commission percentage, 2 more initials and the second percentage; i.e. ABØ25JBØ75. Not shown is the part where I compute 2 character initials. Also, this had to ignore initials of other people who are not subject to commission.
Case 10
FirstCommPers = Left(Cells(Missio, 14), 2)
SecCommPers = Mid(Cells(Missio, 14), 6, 2)
FirstCommAmt = Mid(Cells(Missio, 14), 3, 3)
SecCommAmt = Right(Cells(Missio, 14), 3)
Select Case FirstCommPers
Case "JR", "DL"
FirstJTD = "=IF(len(" & Cells(Missio, 14).Address & ")=10,if(left(" & _
Cells(Missio, 14).Address & ",2)=" & Chr(34) & FirstCommPers & Chr(34) _
& ",(" & Cells(Missio, 10).Address & "" & Cells(Missio, 7).Address & ")*0.2*" _
& "abs(mid(" & Cells(Missio, 14).Address & ",3,3))/100))"
FirstMTD = ((Cells(Missio, 15)  Cells(Missio, 16)) * (FirstCommAmt / 100))
Case "BS", "SD"
FirstJTD = "=IF(len(" & Cells(Missio, 14).Address & ")=10,if(left(" & _
Cells(Missio, 14).Address & ",2)=" & Chr(34) & FirstCommPers & Chr(34) _
& ",(" & Cells(Missio, 10).Address & "" & Cells(Missio, 7).Address & ")*0.05*" _
& "abs(mid(" & Cells(Missio, 14).Address & ",3,3))/100))"
FirstMTD = ((Cells(Missio, 15)  Cells(Missio, 16)) * (FirstCommAmt / 100))
Case Else
FirstJTD = 0
FirstMTD = 0
End Select
Select Case SecCommPers
Case "JR", "DL"
SecJTD = "IF(len(" & Cells(Missio, 14).Address & ")=10,if(mid(" & _
Cells(Missio, 14).Address & ",6,2)=" & Chr(34) & SecCommPers & Chr(34) _
& ",(" & Cells(Missio, 10).Address & "" & Cells(Missio, 7).Address & ")*0.2*" _
& "abs(mid(" & Cells(Missio, 14).Address & ",8,3))/100))"
SecMTD = ((Cells(Missio, 15)  _
Cells(Missio, 16)) * (FirstCommAmt / 100))
Case "BS", "SD"
SecJTD = "IF(len(" & Cells(Missio, 14).Address & ")=10,if(mid(" & _
Cells(Missio, 14).Address & ",6,2)=" & Chr(34) & SecCommPers & Chr(34) _
& ",(" & Cells(Missio, 10).Address & "" & Cells(Missio, 7).Address & ")*0.05*" _
& "abs(mid(" & Cells(Missio, 14).Address & ",8,3))/100))"
SecMTD = ((Cells(Missio, 15)  Cells(Missio, 16)) * (FirstCommAmt / 100))
Case Else
SecJTD = 0
SecMTD = 0
End Select
If FirstJTD = 0 Then
JTDComm = "=" & SecJTD
Else
JTDComm = FirstJTD & "+" & SecJTD
End If
MTDcomm = FirstMTD + SecMTD
range("O" & Missio).formula = JTDComm
range("Q" & Missio).formula = "=IF(iserror(" & Cells(Missio, 15).Address & "" & _
Cells(Missio, 16).Address & "),0," & Cells(Missio, 15).Address & "  " & _
Cells(Missio, 16).Address & ")"
The resulting formula looks like this in the spreadsheet:=IF(LEN($N$80)=10,IF(LEFT($N$80,2)="BS",($J$80$G$80)*0.05*ABS(MID($N$80,3,3))/100))+IF(LEN($N$80)=10,IF(MID($N$80,6,2)="DL",($J$80$G$80)*0.2*ABS(MID($N$80,8,3))/100)) Glad I only had to type it once to figure out how to generate it in code.
