tonywatsonhelp
Well-known Member
- Joined
- Feb 24, 2014
- Messages
- 3,210
- Office Version
- 365
- 2019
- 2016
- Platform
- Windows
Hi Everyone,
I nice complicated one this time.
I'll try explain the best I can, please ask if you need anything clarified.
I have a macro that goes down my calendar sheet and adds in the dates of all my apointments
The problem is that if I already have an appointment that day it gets overwritten.
so I want to edit it if I can to Add a new line if the date is already in use, but only if it needs to.
or example it might add an appointment for 12/04/19 then the next appointment is for 07/06/19 then the next 12/04/19 again, so it adds a new line and inputs the date below,
then carries on inserting dates into the original row but it get a date for 07/06/19, another conflict, but we already added a row so this time we don't need it add just add the date in the second row.
hope that makes sense, think of it like you where writing in a calendar if there's nothing in that date you type in the first row if there is you type in the next row down
Heres the code I use at the moment,
any help would be great
thanks
Tony
Sub Insert_Project_Deadline_Dates()
firstDate = Sheets("Project Deadlines").Range("S10").Value
Lastrow = Sheets("Project Deadlines Raw").Cells(Rows.Count, "A").End(xlUp).Row
For thisRow = 2 To Lastrow
foundRow = Application.Match(Sheets("Project Deadlines Raw").Cells(thisRow, "A").Value, Sheets("Project Deadlines").Range("D:D"), 0)
Pref = Sheets("Project Deadlines Raw").Cells(thisRow, "D")
If Not IsError(thisRow) Then
thisDate = Sheets("Project Deadlines Raw").Cells(thisRow, "C").Value
Sheets("Project Deadlines").Cells(foundRow, thisDate - firstDate + 19).Value = Pref
End If
Next thisRow
End Sub
I nice complicated one this time.
I'll try explain the best I can, please ask if you need anything clarified.
I have a macro that goes down my calendar sheet and adds in the dates of all my apointments
The problem is that if I already have an appointment that day it gets overwritten.
so I want to edit it if I can to Add a new line if the date is already in use, but only if it needs to.
or example it might add an appointment for 12/04/19 then the next appointment is for 07/06/19 then the next 12/04/19 again, so it adds a new line and inputs the date below,
then carries on inserting dates into the original row but it get a date for 07/06/19, another conflict, but we already added a row so this time we don't need it add just add the date in the second row.
hope that makes sense, think of it like you where writing in a calendar if there's nothing in that date you type in the first row if there is you type in the next row down
Heres the code I use at the moment,
any help would be great
thanks
Tony
Sub Insert_Project_Deadline_Dates()
firstDate = Sheets("Project Deadlines").Range("S10").Value
Lastrow = Sheets("Project Deadlines Raw").Cells(Rows.Count, "A").End(xlUp).Row
For thisRow = 2 To Lastrow
foundRow = Application.Match(Sheets("Project Deadlines Raw").Cells(thisRow, "A").Value, Sheets("Project Deadlines").Range("D:D"), 0)
Pref = Sheets("Project Deadlines Raw").Cells(thisRow, "D")
If Not IsError(thisRow) Then
thisDate = Sheets("Project Deadlines Raw").Cells(thisRow, "C").Value
Sheets("Project Deadlines").Cells(foundRow, thisDate - firstDate + 19).Value = Pref
End If
Next thisRow
End Sub