I have created the following code to accomplish the following:
1. Identify all project less than 90 days old
2. If the project is less than 90 days old identify if the commission will be split and if so with which sales person and at what amount.(this is the object of the UserForm)
3. Add the project information for the sales commission split to the worksheet. In other words, copy the entire row for that project to the bottom of the spreadsheet and update the two cells with the input from the userform.
There are 3 projects that meet the less than 90 days criteria that I wish to add to the worksheet via the UserForm, but with my code it adds the first entry (project #1058.) I need the code to loop back to the spreadsheet, identify the next project (in this case project #1082.) that is less than 90 days and if that commission will be split add the project information to the worksheet for the commission split.
Lastly, the salesperson is only eligible to receive the commission once so I also need to flag the entries so that the next time its run they won’t be eligible to get the commission again.
I am also sure there is a more efficient way to do this, just not sure what that is.
Any help would be appreciated. Thanks
'Determine if Service Agreement is the Initial Booking.
Sub Service_Agreement_Incentive_MsgBox()
Application.EnableCancelKey = xlDisabled
Dim InitBookg As Integer
Dim Split As Integer
Dim I As Integer
I = 2
Do While Not (IsEmpty(Cells(I, 2)))
If Cells(I, 2) = "Service_Agreement_1" Or Cells(I, 2) = "Service_Agreement_3" Or Cells(I, 2) = "Service_Agreement_5" Then
If Cells(I, 49) <= 90 And Cells(I, 53) <> "X" Then
InitBookg = MsgBox(Prompt:="Is this the Initial Booking of Project #" & Cells(I, 1) & "?", Buttons:=vbYesNo, Title:="Booking Status")
If InitBookg = vbYes Then
Cells(I, 8) = "Yes"
Call AddServiceAgreementIncentive
Split = MsgBox(Prompt:="Will Project " & "#" & Cells(I, 1) & " be split with another Salesperson" & "?", Buttons:=vbYesNo, Title:="Sales Incentive Split")
If Split = vbYes Then
Call ShowForm
End If
Else
Cells(I, 8) = "No"
End If
UserForm code to copy row and add to bottom of worksheet with 2nd salesperson and commission pertentage
Sub OKButton_Click()
Dim Confirm As Integer
Dim I As Integer
I = 2
Sheets("Model").Select
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
If Cells(I, 49) <= 90 And Cells(I, 53) = "X" Then
Confirm = MsgBox(Prompt:="Please Confirm Project " & "#" & Cells(I, 1) & " be split with another Salesperson" & "?", Buttons:=vbYesNo, Title:="Sales Incentive Split")
If Confirm = vbYes Then
Cells(I, 1).Resize(1, 53).Copy
Cells(FinalRow, 1).Select
NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
Cells(NextRow, 1).Select
ActiveSheet.Paste
ActiveCell.Offset(0, 9) = Salesperson.Value
ActiveCell.Offset(0, 53) = Split.Value
Unload UserForm1 ‘close UserForm
Call Service_Agreement_Incentive_MsgBox ‘Go back to original Macro
End If
End If
End Sub
Bookmarks