Add "placeholder" row to data set based on dates

[katekoz]

Hello,
I have a set of data that keeps a rolling 12 months (currently Feb '19 - Jan '20). I have an InputBox asking for a start date of an employee and, for all rows of data that exist for that employee before the start date, I need a "-1" placed in column S. I have written the code to do that, and have pasted it below.

I have another condition, however, that I need addressed and I CANNOT figure out how to execute! If a month exists in the data set, but the user doesn't have a row of data for that month, then I need to create a "placeholder" row with the username, that month and year, and a "-1" in column S.

The end goal is that every month that exists in the data set before the start date of the employee should have at least one line of data (even if it's just a "placeholder" line) with the username in D and the "-1" in S. Again, if a line of data already exists, I have the -1 added, but I need a way to add rows for the months that don't have data.

Some additional information: In the data set, I have the year in column A, the month in Column B, and the full date of the first of the month (mm/01/yyyy) in column T.

Thanks so much for any help you can offer with this!! It's driving me CRAZY! lol

Kate

Dim month As Integer
Dim year As Integer
Dim r As Integer
Dim lastlog As Long
Dim name As String
Dim note As String
Dim maxdate As Date



UserID = InputBox("Enter the username you'd like to add a start date for.", vbOKCancel)
    If UserID = vbNullString Then Exit Sub
DateID = InputBox("Enter the start date of the employee.", vbOKCancel)
    If DateID = vbNullString Then Exit Sub

ThisWorkbook.Sheets("Training").Range("E2").Value = UserID
ThisWorkbook.Sheets("Training").Range("F2").Value = DateID

year = ThisWorkbook.Sheets("Training").Range("F3").Value
month = ThisWorkbook.Sheets("Training").Range("E3").Value
firstdate = ThisWorkbook.Sheets("Training").Range("G2").Value

'Check for username in data set ("Overall User Data" and then "New Data Add") - alert if there is no data for this user.
 
With ThisWorkbook.Sheets("Overall User Data").Columns(4)
    Set Fnd = .Find(UserID, , , xlWhole, , , False, , False)
End With
If Fnd Is Nothing Then
    ans = MsgBox("This user is not found in the dataset. Please add them as a new user first, then adjust the start date.")
End If

'Change all existing rows of dates from blank to "-1" in S

For r = ThisWorkbook.Sheets("Overall User Data").UsedRange.Rows.Count To 1 Step -1
    If ThisWorkbook.Sheets("Overall User Data").Cells(r, "A") = year Then
        If ThisWorkbook.Sheets("Overall User Data").Cells(r, "B") < month Then
            If ThisWorkbook.Sheets("Overall User Data").Cells(r, "D") = UserID Then
                ThisWorkbook.Sheets("Overall User Data").Cells(r, "S").Value = "-1"
            End If
        End If
    End If
Next

For r = ThisWorkbook.Sheets("Overall User Data").UsedRange.Rows.Count To 1 Step -1
    If ThisWorkbook.Sheets("Overall User Data").Cells(r, "A") < year Then
        If ThisWorkbook.Sheets("Overall User Data").Cells(r, "D") = UserID Then
            ThisWorkbook.Sheets("Overall User Data").Cells(r, "S").Value = "-1"
        End If
    End If
Next

'Add a "placeholder" row for any month/year before the start date that doesn't already have data with a "-1" in S.

 

[DanteAmor]

In a copy of your sheet do the following and check if it is what you need

Sub test()
  Dim UserId, DateID
  Dim iYear As Integer, iMonth As Integer, firstdate As Variant
  Dim i As Long, lr As Long, y As Long, y1 As Variant, m1 As Variant
  Dim Fnd As Range, dic As Object
  Dim startYM As Long, endYM As Long
  
  Application.ScreenUpdating = False
  Set dic = CreateObject("Scripting.Dictionary")
  
  UserId = InputBox("Enter the username you'd like to add a start date for.", vbOKCancel)
  If UserId = vbNullString Then Exit Sub
  DateID = InputBox("Enter the start date of the employee.", vbOKCancel)
  If DateID = vbNullString Then Exit Sub
  UserId = "amor"
  With Sheets("Training")
    .Range("E2").Value = UserId
    .Range("F2").Value = DateID
    iYear = .Range("F3").Value
    iMonth = .Range("E3").Value
    firstdate = .Range("G2").Value
  End With
  
  With Sheets("Overall User Data")
    'Check for username in data set ("Overall User Data" and then "New Data Add") - alert if there is no data for this user.
    Set Fnd = .Range("D:D").Find(UserId, , , xlWhole, , , False, , False)
    If Fnd Is Nothing Then
      MsgBox ("This user is not found in the dataset. Please add them as a new user first, then adjust the start date.")
      Exit Sub
    End If
    
    For i = 1 To .Range("A" & Rows.Count).End(xlUp).Row
      If (.Cells(i, "A") = iYear And .Cells(i, "B") < iMonth And .Cells(i, "D") = UserId) Or _
         (.Cells(i, "A") < iYear And .Cells(i, "D") = UserId) Then
        .Cells(i, "S").Value = "-1"
        dic(.Cells(i, "A") & "|" & .Cells(i, "B")) = Empty
      End If
    Next
    
    i = 2     'inital month
    y = 2019  'initial year
    startYM = DateSerial(y, i, 1)
    endYM = DateSerial(iYear, iMonth - 1, 1)
    Do While startYM <= endYM
      y1 = year(CDate(startYM))
      m1 = month(CDate(startYM))
      If Not dic.exists(y1 & "|" & m1) Then
        lr = .Range("A" & Rows.Count).End(xlUp).Row + 1
        .Range("A" & lr) = y1
        .Range("B" & lr) = m1
        .Range("D" & lr) = UserId
        .Range("S" & lr) = "-1"
      End If
      i = i + 1
      startYM = DateSerial(y, i, 1)
    Loop
  End With
End Sub

 

[katekoz]

That worked! I just had to take out the line where you set UserID to your name (for testing), and it's perfect! Thank you so much for this!

Thank you also for combining the two section where I searched for the previous year and the current year / previous date. I figured there was a way to make that more elegant, but wasn't sure how. I'm going to study this pretty closely - I think I can learn alot from it!

Thank you again!!!!

 

[DanteAmor]

I just had to take out the line where you set UserID to your name (for testing)

Sorry for that, I forgot to remove that line.

I'm glad to help you. Thanks for the feedback.

 

[katekoz]

Hi DanteAmor - I just noticed one thing that won't work in the code above. You set i=2 and y=2019, which is correct for now, but not always. The data set is a rolling 12 months, so when data is added for next month, this will no longer be correct.
y is easy, as it's simply MIN(A:A)
i, however, needs to be the MIN(B:B) of the set of rows with Column A value of y
How would I set those values for y and i so that it's always looking for that oldest month/year combo?
Kate

 

[DanteAmor]

Try this

Sub test()
  Dim UserId, DateID
  Dim iYear As Integer, iMonth As Integer, firstdate As Variant
  Dim i As Long, lr As Long, y As Long, y1 As Variant, m1 As Variant
  Dim Fnd As Range, dic As Object
  Dim startYM As Long, endYM As Long
  
  Application.ScreenUpdating = False
  Set dic = CreateObject("Scripting.Dictionary")
  
  UserId = InputBox("Enter the username you'd like to add a start date for.", vbOKCancel)
  If UserId = vbNullString Then Exit Sub
  DateID = InputBox("Enter the start date of the employee.", vbOKCancel)
  If DateID = vbNullString Then Exit Sub
  With Sheets("Training")
    .Range("E2").Value = UserId
    .Range("F2").Value = DateID
    iYear = .Range("F3").Value
    iMonth = .Range("E3").Value
    firstdate = .Range("G2").Value
  End With
  
  With Sheets("Overall User Data")
    'Check for username in data set ("Overall User Data" and then "New Data Add") - alert if there is no data for this user.
    Set Fnd = .Range("D:D").Find(UserId, , , xlWhole, , , False, , False)
    If Fnd Is Nothing Then
      MsgBox ("This user is not found in the dataset. Please add them as a new user first, then adjust the start date.")
      Exit Sub
    End If
    
    lr = .Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To lr
      If (.Cells(i, "A") = iYear And .Cells(i, "B") < iMonth And .Cells(i, "D") = UserId) Or _
         (.Cells(i, "A") < iYear And .Cells(i, "D") = UserId) Then
        .Cells(i, "S").Value = "-1"
        dic(.Cells(i, "A") & "|" & .Cells(i, "B")) = Empty
      End If
    Next
    
    y = WorksheetFunction.Min(.Range("A1:A" & lr))                      'initial year
    i = Evaluate("=MIN(IF(A1:A" & lr & "=" & y & ",B1:B" & lr & "))")   'inital month

    startYM = DateSerial(y, i, 1)
    endYM = DateSerial(iYear, iMonth - 1, 1)
    Do While startYM <= endYM
      y1 = year(CDate(startYM))
      m1 = month(CDate(startYM))
      If Not dic.exists(y1 & "|" & m1) Then
        lr = .Range("A" & Rows.Count).End(xlUp).Row + 1
        .Range("A" & lr) = y1
        .Range("B" & lr) = m1
        .Range("D" & lr) = UserId
        .Range("S" & lr) = "-1"
      End If
      i = i + 1
      startYM = DateSerial(y, i, 1)
    Loop
  End With
End Sub

 

[katekoz]

This one did something odd. I tested it with a start date of 10/1/19, so it should have added a row for each month 2/1/19-9/1/19, which it did. It also, however, added a row for 12/18 and for 1/19. Not sure why it added those two extra months.

 

[DanteAmor]

the year and start month calculates it from the date column. The final year and month take it from the cells F3 and E3.

 

[katekoz]

F3 and E3 are just little formula cells. Basically, when the macro puts the "start date" from the input box into cell F2, cell E3 has the formula =MONTH(F2) and F3 has the formula =YEAR(F2).

The macro is basically working correctly, it's just that it's adding lines for 12/18 and 1/19, when the data set only has data from 2/19, so it's adding lines for months that are too old for the data set.

 

[DanteAmor]

puts the "start date"

Then I misunderstood.
You don't set the initial date. The start date is calculated with the minimum values of columns A and B:

The data set is a rolling 12 months, so when data is added for next month, this will no longer be correct.
y is easy, as it's simply MIN(A:A)
i, however, needs to be the MIN(B:B) of the set of rows with Column A value of y
How would I set those values for y and i so that it's always looking for that oldest month/year combo?

If you want to set the initial year and month, then use the macro of post # 2:

    i = 2     'inital month
    y = 2019  'initial year