Okay, I found a solution that is to remove the section of code that changes the deepest IF command's else from blanking a cell to doing nothing, and clearing the entire sheet at the start of the code. Now it works as intended, and gets me multiple entries per person. I just still have no idea how that tiny bit managed to mess up everything...
Posts by ShoutingMonkey
-
-
In Excel 2010, I am currently trying to make a calendar that will auto-populate based on a list of things people want to do at specific intervals. I have uploaded a demonstration sheet with the code that just refuses to behave, and will follow with a duplicate below along with my (bad) attempt at explaining what I want to do:
In this given example, I have a row of dates from feb 1st to feb 28th on a sheet ("Output"), and below that, two rows that each have a different name (in this case, Tester and Something). My desire is to have something like below: [TABLE="border: 1, cellpadding: 1, width: 500"]
[tr]
[td][/td]
[td]1st feb
[/td]
[td]2nd feb
[/td]
[td]3rd feb
[/td]
[td]4th feb
[/td]
[td]5th feb
[/td]
[td]etc...
[/td]
[td]27th feb
[/td]
[td]28th feb
[/td]
[/tr]
[tr]
[td]Tester
[/td]
[td]Coffee
[/td]
[td]Coffee
[/td]
[td][/td]
[td]Panic
[/td]
[td][/td]
[td][/td]
[td][/td]
[td]Meeting
[/td]
[/tr]
[tr]
[td]Something
[/td]
[td][/td]
[td]Meeting
[/td]
[td]Meeting
[/td]
[td]Meeting
[/td]
[td][/td]
[td][/td]
[td]Napping
[/td]
[td][/td]
[/tr]
[tr]
[/TABLE]
In another sheet, "Input", I let a user type into a table their name, the name of the task they are doing, and the dates they are doing it on. As so: [TABLE="border: 1, cellpadding: 1, width: 500"]
[td]Name
[/td]
[td]Task
[/td]
[td]Start date
[/td]
[td]End date
[/td]
[/tr]
[tr]
[td]Tester
[/td]
[td]Meeting
[/td]
[td]02-02-2018
[/td]
[td]05-02-2018
[/td]
[/tr]
[tr]
[td]Tester
[/td]
[td]Coffee
[/td]
[td]08-02-2018
[/td]
[td]20-02-2018
[/td]
[/tr]
[tr]
[td]Something
[/td]
[td]Coffee
[/td]
[td]08-02-2018
[/td]
[td]08-02-2018
[/td]
[/tr]
[/TABLE]
Now, my code works fine, providing there's only one available set of dates per person. The moment Tester wants to have a Meeting and Coffee, VBA for some reason completely ignores the former's existence and only adds one thing to the list of dates. In the case above, only the two Coffee entries would show up in the calendar; one for Tester and one for Something. If I delete Tester's Coffee task, then it finds the Meeting just fine.My code, which is linked to a button (the table-sheet is "Input", while the resulting calendar is "Output"). Please note that due to reasons, the calendar dates are in row 5, and there are three blank columns between the names on the Calendar and the start of the Calendar itself (I have to keep those free for other data)
My first of three nestled loops goes through each "Output" row barring the one with the dates, one by one. Inside that loop is another loop, which goes through each column in the "Output". And then the last loop inside that is one that runs through every row on the "Input" sheet. My idea is then that it takes the first column in the "Input" sheet and compares the name listed there to the name listed in the first column of the "Output" sheet; if true, it will then take the current column's date on "Output", and look into columns 3 and 4 of the "Input" sheet to see if the Output date falls within the listed Input dates. If true, it is meant to take the results from "Input"'s second column. Rinse and repeat for each cell.
There are probably more elegant ways to do it, but I am still very much learning VBA. But right now, I am simply stumped as to how the heck it skips the first row(s) of Input for a person while slotting things into the calendar. Help?
[VBA]'dim loops
Dim countcol As Long
Dim countrow As Long
Dim inputrow As Long
Dim inputcol As Long'dim vars
Dim rownum As Long
Dim colnum As Long
Dim inputrownum As Longrownum = 6 'start row for primary loop
For countrow = 1 To 2 '0 'calendar ws rows
colnum = 5 'reset for repeat loop
For countcol = 1 To 28 '0 'calendar ws columns
inputrownum = 2 'reset for repeat loop
For inputrow = 1 To 10 'input ws rows
'if input name = output name
If Sheets("Output").Cells(rownum, 1) = Sheets("Input").Cells(inputrownum, 1) Then
'if date is above/equal start and below/equal end
If Sheets("Output").Cells(5, colnum) >= Sheets("Input").Cells(inputrownum, 3) And Sheets("Output").Cells(5, colnum) <= Sheets("Input").Cells(inputrownum, 4) Then
'fill in text
Sheets("Output").Cells(rownum, colnum) = Sheets("Input").Cells(inputrownum, 2)
Else
'blank
Sheets("Output").Cells(rownum, colnum) = ""
End If
End If
inputrownum = inputrownum + 1 'increase in ws row var
Next inputrow
colnum = colnum + 1 'increase calendar ws column var
Next countcol
rownum = rownum + 1 'increase calendar ws row var
Next countrow
[/VBA]