Hi everyone,
I have this code below that for the most part works well but needs help. When I have dates for employees in any column (it does float so not always in col C) i need to know the Time Period in the cell to the left. This list can be quite long or as short as you see now. Currently with the code below, I highlight the entire column and run the vba which inserts a column to the left and the the verbage needed depending on the date. Most of the time it works. Problem I run into is sometimes it doesn't work at all and i don't know why. But it also pastes the results as formulas instead of values. I am not sure what to change to get it to always work and also to put the results in as values instead of formulas so any and all help is greatly appreciated.
Before:
After:
I have this code below that for the most part works well but needs help. When I have dates for employees in any column (it does float so not always in col C) i need to know the Time Period in the cell to the left. This list can be quite long or as short as you see now. Currently with the code below, I highlight the entire column and run the vba which inserts a column to the left and the the verbage needed depending on the date. Most of the time it works. Problem I run into is sometimes it doesn't work at all and i don't know why. But it also pastes the results as formulas instead of values. I am not sure what to change to get it to always work and also to put the results in as values instead of formulas so any and all help is greatly appreciated.
Before:
Worker ID | Worker Name | Check Date |
1027 | Sorensen,Robert | 06/15/2021 |
1027 | Sorensen,Robert | 07/04/2021 |
1030 | Dickinson,Timothy | 06/15/2021 |
1030 | Dickinson,Timothy | 07/04/2021 |
1034 | Mooney,Brad | 06/15/2021 |
1034 | Mooney,Brad | 07/04/2021 |
1041 | Adams,Kathleen | 06/15/2021 |
1041 | Adams,Kathleen | 07/04/2021 |
1041 | Adams,Kathleen | 10/01/2021 |
1042 | Anderson,Christopher | 06/15/2021 |
1042 | Anderson,Christopher | 07/04/2021 |
1055 | Mills-Halso,Susan | 06/15/2021 |
1055 | Mills-Halso,Susan | 07/04/2021 |
1079 | McBride,Rodney | 06/15/2021 |
1079 | McBride,Rodney | 07/04/2021 |
1089 | Vandewege,Daniele | 06/15/2021 |
1089 | Vandewege,Daniele | 07/04/2021 |
1101 | Schricker Jr,Jerry | 06/15/2021 |
1101 | Schricker Jr,Jerry | 07/04/2021 |
1133 | Misiewicz,Stacey | 06/15/2021 |
1133 | Misiewicz,Stacey | 07/04/2021 |
1184 | Tanis,Wendy | 06/15/2021 |
1184 | Tanis,Wendy | 07/04/2021 |
1185 | Priebe,Dean | 06/15/2021 |
After:
12114820 YTD Import.xlsx | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | Worker ID | Worker Name | Time Period | Check Date | ||
2 | 1027 | Sorensen,Robert | Check Date - Qtr 2 | 06/15/2021 | ||
3 | 1027 | Sorensen,Robert | Check Date - Qtr 3 | 07/04/2021 | ||
4 | 1030 | Dickinson,Timothy | Check Date - Qtr 2 | 06/15/2021 | ||
5 | 1030 | Dickinson,Timothy | Check Date - Qtr 3 | 07/04/2021 | ||
6 | 1034 | Mooney,Brad | Check Date - Qtr 2 | 06/15/2021 | ||
7 | 1034 | Mooney,Brad | Check Date - Qtr 3 | 07/04/2021 | ||
8 | 1041 | Adams,Kathleen | Check Date - Qtr 2 | 06/15/2021 | ||
9 | 1041 | Adams,Kathleen | Check Date - Qtr 3 | 07/04/2021 | ||
10 | 1041 | Adams,Kathleen | Check Date - Qtr 4 | 10/01/2021 | ||
11 | 1042 | Anderson,Christopher | Check Date - Qtr 2 | 06/15/2021 | ||
12 | 1042 | Anderson,Christopher | Check Date - Qtr 3 | 07/04/2021 | ||
13 | 1055 | Mills-Halso,Susan | Check Date - Qtr 2 | 06/15/2021 | ||
14 | 1055 | Mills-Halso,Susan | Check Date - Qtr 3 | 07/04/2021 | ||
15 | 1079 | McBride,Rodney | Check Date - Qtr 2 | 06/15/2021 | ||
16 | 1079 | McBride,Rodney | Check Date - Qtr 3 | 07/04/2021 | ||
17 | 1089 | Vandewege,Daniele | Check Date - Qtr 2 | 06/15/2021 | ||
18 | 1089 | Vandewege,Daniele | Check Date - Qtr 3 | 07/04/2021 | ||
19 | 1101 | Schricker Jr,Jerry | Check Date - Qtr 2 | 06/15/2021 | ||
20 | 1101 | Schricker Jr,Jerry | Check Date - Qtr 3 | 07/04/2021 | ||
21 | 1133 | Misiewicz,Stacey | Check Date - Qtr 2 | 06/15/2021 | ||
22 | 1133 | Misiewicz,Stacey | Check Date - Qtr 3 | 07/04/2021 | ||
23 | 1184 | Tanis,Wendy | Check Date - Qtr 2 | 06/15/2021 | ||
24 | 1184 | Tanis,Wendy | Check Date - Qtr 3 | 07/04/2021 | ||
25 | 1185 | Priebe,Dean | Check Date - Qtr 2 | 06/15/2021 | ||
Sheet3 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C2:C25 | C2 | ="Check Date - Qtr "&ROUNDUP(MONTH(D2)/3,0) |
VBA Code:
Sub PPP_Time_Period()
Dim sht As Worksheet
Dim lastrow As Long
Set sht = ActiveSheet
lastrow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
Selection.Insert Shift:=xlToRight
ActiveCell = "Time Period"
ActiveCell.Offset(1, 0).FormulaR1C1 = "=""Check Date - Qtr ""&ROUNDUP(MONTH(RC[1])/3,0)"
ActiveCell.Offset(1, 0).Copy Range(ActiveCell.Offset(1, 0), Cells(lastrow, ActiveCell.Column))
End Sub