Add "placeholder" row to data set based on dates

katekoz

Board Regular
Joined
Jan 20, 2020
Messages
90
Office Version
2016
Platform
Windows
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!!
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,207
Office Version
2007
Platform
Windows
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

Board Regular
Joined
Jan 20, 2020
Messages
90
Office Version
2016
Platform
Windows
Here is a snapshot of my date table:
1582055837285.png


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

Board Regular
Joined
Jan 20, 2020
Messages
90
Office Version
2016
Platform
Windows
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.
1582056812478.png
 

katekoz

Board Regular
Joined
Jan 20, 2020
Messages
90
Office Version
2016
Platform
Windows
I think the problem in the macro is here:

VBA Code:
    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

Board Regular
Joined
Jan 20, 2020
Messages
90
Office Version
2016
Platform
Windows
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

Well-known Member
Joined
Dec 3, 2018
Messages
10,207
Office Version
2007
Platform
Windows
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

Board Regular
Joined
Jan 20, 2020
Messages
90
Office Version
2016
Platform
Windows
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

Board Regular
Joined
Jan 20, 2020
Messages
90
Office Version
2016
Platform
Windows
How the macro looks now:
VBA Code:
  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
 

Forum statistics

Threads
1,089,640
Messages
5,409,479
Members
403,265
Latest member
HMR120

This Week's Hot Topics

Top