NorthbyNorthwest
Board Regular
- Joined
- Oct 27, 2013
- Messages
- 154
- Office Version
- 365
Hi, everyone. Need help with task. I have a two tables (list objects) in a workbook. Table 1 has a list of assignments (100- 150) similar to :
<tbody>
</tbody>
Table 2 is aw weekly report with current inventory of open cases that make up lists. Runs between 3,000 and 5,000. Structured like so:
<tbody>
</tbody>
I want to update the # pending in column 7 of Table 1 using code that would first look at status in column 8 of Table 1. If status is RE or CL, I don't want anything to happen. I want data in cell to remain as is. If, however, the status in column 8 is IP, I want to count the number of cases on the weekly update (Table 2) based on the date ranges (begin and end dates) in Table 1 and file date on weekly report. I was unsuccessful in coming up with a formula so now I'm trying code and struggling. I'm trying to use a for-next loop and worksheetfunction.countifs. See code below. Any help anyone can offer would be greatly appreciated.
List ID | Begin Date | End Date | Count | Assigned on | Assigned to | # Pending | Status | Status Date |
L0001A | 05/24/14 | 06/06/14 | 275 | 03/16/15 | Emp 1 | 1 | IP | 03/23/16 |
L0002A | 08/16/14 | 08/26/14 | 256 | 03/16/15 | Emp 2 | 32 | RE | 03/23/16 |
L0002R | 08/16/14 | 08/26/14 | 32 | 03/03/16 | Emp 3 | 1 | IP | 03/23/16 |
L0003A | 09/17/14 | 09/24/14 | 259 | 03/16/15 | Emp 4 | 0 | CL | 03/03/16 |
L0004A | 10/31/14 | 11/06/14 | 262 | 05/05/15 | Emp 6 | 1 | IP | 03/23/16 |
L0005A | 01/05/15 | 01/07/15 | 236 | 06/02/15 | Emp 7 | 1 | IP | 03/23/16 |
L0006A | 01/08/15 | 01/12/15 | 269 | 06/02/15 | Emp 8 | 1 | IP | 03/23/16 |
L0007A | 01/13/15 | 01/15/15 | 243 | 06/02/15 | Emp 9 | 3 | IP | 03/23/16 |
L0008A | 01/21/15 | 01/23/15 | 234 | 06/16/15 | Emp 10 | 2 | IP | 03/23/16 |
L0009A | 01/24/15 | 01/28/15 | 280 | 06/16/15 | Emp 11 | 8 | RE | 03/03/16 |
L0009R | 01/24/15 | 01/28/15 | 8 | 03/03/16 | Emp 12 | 2 | IP | 03/23/16 |
L0010A | 01/29/15 | 02/02/15 | 286 | 06/16/15 | Emp 13 | 12 | CL | 03/23/16 |
<tbody>
</tbody>
Table 2 is aw weekly report with current inventory of open cases that make up lists. Runs between 3,000 and 5,000. Structured like so:
File Num | Last Name | File Date | Client ID | Caseworker |
123 | SMITH | 05/28/14 | AAA | Employee 1 |
456 | JONES | 08/22/14 | BBB | Employee 2 |
789 | BROWN | 09/18/14 | CCC | Employee 3 |
122 | WHITE | 10/31/14 | ABC | Employee 4 |
455 | WILLIAMS | 01/05/15 | DEF | Employee 5 |
788 | HILL | 01/08/15 | GHI | Employee 6 |
721 | GREEN | 01/13/15 | DDD | Employee 7 |
358 | JOHNSON | 01/14/15 | EEE | Employee 8 |
597 | THOMAS | 01/14/15 | FFF | Employee 9 |
721 | CARTER | 01/23/15 | GGG | Employee 10 |
333 | ADAMS | 01/23/15 | HHH | Employee 11 |
237 | WASHINGTON | 01/25/15 | JKL | Employee 7 |
358 | ROBINSON | 01/28/15 | MNO | Employee 2 |
252 | BROOKS | 01/29/15 | PQR | Employee 3 |
987 | HARRIS | 01/29/15 | III | Employee 1 |
<tbody>
</tbody>
I want to update the # pending in column 7 of Table 1 using code that would first look at status in column 8 of Table 1. If status is RE or CL, I don't want anything to happen. I want data in cell to remain as is. If, however, the status in column 8 is IP, I want to count the number of cases on the weekly update (Table 2) based on the date ranges (begin and end dates) in Table 1 and file date on weekly report. I was unsuccessful in coming up with a formula so now I'm trying code and struggling. I'm trying to use a for-next loop and worksheetfunction.countifs. See code below. Any help anyone can offer would be greatly appreciated.
Code:
Sub CountRemainder()
Dim i As Integer
Dim FinalRow As Long
FinalRow = ThisWorkbook.Worksheets("List").Range("A" & Rows.Count).End(xlUp).Row
For i = 6 To FinalRow
If ThisWorkbook.Worksheets("List").Range.Cells(i, 8).Value <> "IP" Then
'do nothing
Else
On Error Resume Next
ThisWorkbook.Worksheets("List").Range.Cells(i, 7).Value = Application.WorksheetFunction.CountIfs(ThisWorkbook.Worksheets("Cases").Range("C5:C"), ">=" & _
ThisWorkbook.Worksheets("List").Range.Cells(i, 2), ThisWorkbook.Worksheets("Cases").Range("C5:C"), "<=" & ThisWorkbook.Worksheets("List").Range.Cells(i, 3))
End If
Next i
End Sub