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.

 

[katekoz]

Oh, I see - my terminology is misleading, I think. The "start date" is the date that the employee started working for the company, AKA "hire date." That's a date that's manually entered into the macro through the InputBox:
DateID = InputBox("Enter the start date of the employee.", vbOKCancel)

Once that start date is entered, each month that exists in the data set needs to have a line added for that month for the user. Currently, the oldest month is the data set is Feb 2019, so I would need a line for each month from Feb '19 through the month/year of the "start date."

Currently, the macro is doing that exact thing, but it's also adding a line for Jan '19 and Dec '18 as well. I want it to stop adding lines at Feb '19, since that's the "oldest" date in the data set.

I hope this is more clear. I think my use of the phrase "start date" was ambiguous. Sorry about that!!

 

[DanteAmor]

I still do not understand.
I am confused with the "start date".
I understand that there is a range of dates you want to get, date "from" and date "to" (do you think it's right to call them that?).

For both dates ("from" and "to"), where do you want to get it:
- inputbox,
- cells of the "Training" sheet
- of columns A and B of the sheet "Overall User Data"

I think you should explain to me where I get the couple of dates.

 

[katekoz]

Here is a snapshot of my date table:

There is a Paid Year and Paid Month in the data set. There is also the Month Year column in T, which is just the formula =DATE([@[Paid Year]],[@[Paid Month]],1).

When adding a new user with a new UserID (say, adding User4) to the data set, there will be the InputBox prompt of what that user's "start date" is. That is basically their "hire date." If that user is hired on, say, 10/1/19, then the macro needs to check the entire data set. For any month that exists in the dataset before 10/1/19, if a row of data exists for that month, it needs to change the value in Column S to -1. The macro does that already. What you're working on now is the case that there is a month in the dataset (say, Feb of 2019) that doesn't have any row of data existing for this user for that month. I would need a new row added to the end of the data set that would have 2019 in A, 2 in B, User4 in D, and -1 in S. (Columns O-T will all autopopulate, as they're formulas built into the table).

The macro, as-is, does add rows for months before the employee's start date (hire date), but it adds too many rows, as it's adding rows for Dec '18 and Jan '19, which are months that don't exist in this data set. The oldest date in Column T is 2/1/19.

So, I need the macro to function exactly as it does now, but just not add Dec '18 or Jan '19, as those dates are too old for the data set.

I'll post another photo in one minute of what's happening in better detail.

 

[katekoz]

Ok, here is a photo of what's happening. I added you as a user to my metrics with a start date (hire date) of 10/1/19. You can see that, since you didn't have any data at all in the set, it added one row for each of the months that exist in the data set with a -1 in column S since your start date. That's what it's supposed to do - it's working right in that sense.
You can see the problem is the two rows with red. The macro added rows for Dec 2018 and Jan 2019. The problem is, those dates aren't in my data set. The oldest date in the data set is Feb 2019, so I need those two rows to not be added.

 

[katekoz]

I think the problem in the macro is here:

    y = WorksheetFunction.Min(.Range("A1:A" & lr))                      'initial year
    i = Evaluate("=MIN(IF(A1:A" & lr & "=" & y & ",B1:B" & lr & "))")   'inital month

I think that's where it's creating Dec 2018 and Jan 2019 because it's not recognizing that the oldest month in the data set is Feb 2019. I'm not certain, though, because you're code is really complicated for me to read! I just don't understand it well enough.

 

[katekoz]

Oh, another thing I just thought could be the issue...
I entered the employee's hire date as 10/1/19. The data set is a rolling twelve month, but that set is currently 2/1/19 - 1/1/20. I'm adding the employee in the middle of my data set, not at the end of it. Maybe that's the issue with the macro?

 

[DanteAmor]

I entered the employee's hire date as 10/1/19.

Sorry, but there are some things I do not understand, the previous date is 1oct2019 or 10ene2019?

In your image I can't see which is the oldest year on the "Overall User Data"

I still don't understand where I'm going to take the start and end dates.

You could upload your test file to the cloud to perform the same test you are doing.


You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[katekoz]

You'll never guess what happened! I was testing the macro by stepping through and watching it add lines to the "Overall User Data" tab, and it worked perfectly! No extra months.

I changed the "With" sheet statement to select the sheet, then added a workbook and sheet callout to all the parts of the macro that used it, and now the macro works perfectly!

There must have been some confusion somewhere in the macro about the sheet reference, though I don't know where. Regardless, I've tested it many many times, and it works exactly right now!!

 

[katekoz]

How the macro looks now:

  ThisWorkbook.Sheets("Overall User Data").Select
    '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 = ThisWorkbook.Sheets("Overall User Data").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 = ThisWorkbook.Sheets("Overall User Data").Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To lr
      If (ThisWorkbook.Sheets("Overall User Data").Cells(i, "A") = iYear And ThisWorkbook.Sheets("Overall User Data").Cells(i, "B") < iMonth And ThisWorkbook.Sheets("Overall User Data").Cells(i, "D") = UserId) Or _
         (ThisWorkbook.Sheets("Overall User Data").Cells(i, "A") < iYear And ThisWorkbook.Sheets("Overall User Data").Cells(i, "D") = UserId) Then
        ThisWorkbook.Sheets("Overall User Data").Cells(i, "S").Value = "-1"
        dic(ThisWorkbook.Sheets("Overall User Data").Cells(i, "A") & "|" & ThisWorkbook.Sheets("Overall User Data").Cells(i, "B")) = Empty
      End If
    Next
    
    y = WorksheetFunction.Min(ThisWorkbook.Sheets("Overall User Data").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 = ThisWorkbook.Sheets("Overall User Data").Range("A" & Rows.Count).End(xlUp).Row + 1
        ThisWorkbook.Sheets("Overall User Data").Range("A" & lr) = y1
        ThisWorkbook.Sheets("Overall User Data").Range("B" & lr) = m1
        ThisWorkbook.Sheets("Overall User Data").Range("D" & lr) = UserId
        ThisWorkbook.Sheets("Overall User Data").Range("S" & lr) = "-1"
      End If
      i = i + 1
      startYM = DateSerial(y, i, 1)
    Loop

 

[DanteAmor]

and it worked perfectly!

I'm glad to know that it already works for you ?