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!

[TABLE="width: 500"]
<tbody>[TR]
[TD]Candidate ID[/TD]
[TD]Date[/TD]
[TD]Sales Number[/TD]
[/TR]
[TR]
[TD]12027[/TD]
[TD]6/30/2019[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]12027[/TD]
[TD]7/1/2019[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]12027[/TD]
[TD]7/2/2019[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]12027[/TD]
[TD]7/4/2019[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]12027[/TD]
[TD]7/5/2019[/TD]
[TD]17[/TD]
[/TR]
[TR]
[TD]18498[/TD]
[TD]7/2/2019[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]18498
[/TD]
[TD]7/3/2019[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]10441[/TD]
[TD]6/30/2019[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]13129[/TD]
[TD]7/4/2019[/TD]
[TD]9[/TD]
[/TR]
</tbody>[/TABLE]

 

[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!

[TABLE="width: 500"]
<tbody>[TR]
[TD]Candidate ID[/TD]
[TD]Date[/TD]
[TD]Sales Number[/TD]
[/TR]
[TR]
[TD]12027[/TD]
[TD]6/30/2019[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]12027[/TD]
[TD]7/1/2019[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]12027[/TD]
[TD]7/2/2019[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]12027[/TD]
[TD]7/4/2019[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]12027[/TD]
[TD]7/5/2019[/TD]
[TD]17[/TD]
[/TR]
[TR]
[TD]18498[/TD]
[TD]7/2/2019[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]18498[/TD]
[TD]7/3/2019[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]10441[/TD]
[TD]6/30/2019[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]13129[/TD]
[TD]7/4/2019[/TD]
[TD]9[/TD]
[/TR]
</tbody>[/TABLE]

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