VB add new row with unique date by candidate id

[Magoosball]

Hi

I'm trying to write a VB script that states from row 2 to last row; if the sales number (Column C) is greater than 10 then create a new row at the bottom of the data set with the same candidate ID, a date that isn't currently tied to that employee number and the sales number minus 10.

The Date should be a Date from the previous work week (Sunday through Saturday so currently 6/30/2019 - 7/6/2019) that doesn't currently exist for this employee. If the candidate happens to already have 7 rows of data for each day of the week the date should report back as "ERRORR - Each Date already exists for this candidate".

The data set below would be a sample starting data set. For example: Candidate ID 12027 should have a new row inserted at the bottom of the data set. The Candidate id would be 12027. The date would be any date in the previous work week that isn't 6/30, 7/1, 7/2, 7/4, or 7/5 land the sales number would be 5. The 2nd row of data wouldn't need a new row added because the sales number is already less than 10. The third row would need a new row added. Candidate 18498 would have 1 new row entered at the bottom of the data set with a date that isn't 7/2/2019 and a sales number of 1.

Thank you in advance!

Candidate ID	Date	Sales Number
12027	6/30/2019	15
12027	7/1/2019	9
12027	7/2/2019	11
12027	7/4/2019	6
12027	7/5/2019	17
18498	7/2/2019	11
18498	7/3/2019	2
10441	6/30/2019	6
13129	7/4/2019	9

<tbody>
</tbody>

 

[Steve_]

Hi

I'm trying to write a VB script that states from row 2 to last row; if the sales number (Column C) is greater than 10 then create a new row at the bottom of the data set with the same candidate ID, a date that isn't currently tied to that employee number and the sales number minus 10.

The Date should be a Date from the previous work week (Sunday through Saturday so currently 6/30/2019 - 7/6/2019) that doesn't currently exist for this employee. If the candidate happens to already have 7 rows of data for each day of the week the date should report back as "ERRORR - Each Date already exists for this candidate".

The data set below would be a sample starting data set. For example: Candidate ID 12027 should have a new row inserted at the bottom of the data set. The Candidate id would be 12027. The date would be any date in the previous work week that isn't 6/30, 7/1, 7/2, 7/4, or 7/5 land the sales number would be 5. The 2nd row of data wouldn't need a new row added because the sales number is already less than 10. The third row would need a new row added. Candidate 18498 would have 1 new row entered at the bottom of the data set with a date that isn't 7/2/2019 and a sales number of 1.

Thank you in advance!

Candidate ID	Date	Sales Number
12027	6/30/2019	15
12027	7/1/2019	9
12027	7/2/2019	11
12027	7/4/2019	6
12027	7/5/2019	17
18498	7/2/2019	11
18498	7/3/2019	2
10441	6/30/2019	6
13129	7/4/2019	9

<tbody>
</tbody>

This should get you a good start. The date part will require significant code.

Sub HereWeGo()
    With Sheets("Sheet1")
        For r = 2 To .UsedRange.Rows.Count
            If Cells(r, 3) > 10 Then
                Cells(.UsedRange.Rows.Count + 1, 1) = Cells(r, 1)
                Cells(.UsedRange.Rows.Count, 2) = Cells(r, 2)   'I simply do not have the time today at work to finish this part.
                Cells(.UsedRange.Rows.Count, 3) = CInt(Cells(r, 3)) - 10
            End If
        Next r
    End With
End Sub