KDavidP1987
Board Regular
- Joined
- Mar 6, 2018
- Messages
- 51
Hello all,
I am trying to find a method to loop through rows in a named table, copying each row over to another table and adding a value in a blank field on the end of each row which sequences the dates between a datespan.
I came across code which can separate a datespan successfully into rows, but have been having trouble creating a loop to go through each row of data and copying the rest over.
Example of data from table (w/ headers):
Table Name: TblOGCalendar
<tbody>
</tbody>
Should be copied over to look like the following:
Table Name: TblR2Calendar
<tbody>
</tbody>
Code to separate date-span:
Thank you, in advance, for any potential solutions!
Sincerely,
Kris
I am trying to find a method to loop through rows in a named table, copying each row over to another table and adding a value in a blank field on the end of each row which sequences the dates between a datespan.
I came across code which can separate a datespan successfully into rows, but have been having trouble creating a loop to go through each row of data and copying the rest over.
Example of data from table (w/ headers):
Table Name: TblOGCalendar
Employee | Category | Start Time | End Time | Event Description | Days | All Day Event |
John Smith | PTO | 1/2/2019 | 1/4/2019 | Vacation | 3 | Yes |
Jane Smith | PTO | 2/5/2019 | 2/7/2019 | Personal | 3 | Yes |
<tbody>
</tbody>
Should be copied over to look like the following:
Table Name: TblR2Calendar
Employee | Category | Start Time | End Time | Event Description | Days | All Day Event | Date |
John Smith | PTO | 1/2/2019 | 1/4/2019 | Vacation | 3 | Yes | 1/2/2019 |
John Smith | PTO | 1/2/2019 | 1/4/2019 | Vacation | 3 | Yes | 1/3/2019 |
John Smith | PTO | 1/2/2019 | 1/4/2019 | Vacation | 3 | Yes | 1/4/2019 |
Jane Smith | PTO | 2/5/2019 | 2/7/2019 | Personal | 3 | Yes | 2/5/2019 |
Jane Smith | PTO | 2/5/2019 | 2/7/2019 | Personal | 3 | Yes | 2/6/2019 |
Jane Smith | PTO | 2/5/2019 | 2/7/2019 | Personal | 3 | Yes | 2/7/2019 |
<tbody>
</tbody>
Code to separate date-span:
Code:
Sub WriteDates()
Dim rng As Range
Dim StartRng As Range
Dim EndRng As Range
Dim OutRng As Range
Dim StartValue As Variant
Dim EndValue As Variant
xTitleId = "KutoolsforExcel"
Set StartRng = Application.Selection
Set StartRng = Application.InputBox("Start Range (single cell):", xTitleId, StartRng.Address, Type:=8)
Set EndRng = Application.InputBox("End Range (single cell):", xTitleId, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
Set OutRng = OutRng.Range("A1")
StartValue = StartRng.Range("A1").Value
EndValue = EndRng.Range("A1").Value
If EndValue - StartValue <= 0 Then
Exit Sub
End If
ColIndex = 0
For i = StartValue To EndValue
OutRng.Offset(ColIndex, 0) = i
ColIndex = ColIndex + 1
Next
End Sub
Thank you, in advance, for any potential solutions!
Sincerely,
Kris