Pats83
07-03-2013, 01:30 PM
StartDate = Application.WorksheetFunction.Index(Sheets("Employee").Range("$A$3:$C$" & EmpRecCount), Application.WorksheetFunction.Match(Sheets("Mpro").Range("A" & i), Sheets("Employee").Range("$B$3:$B$" & EmpRecCount), 0), 3)
EndDate = Application.WorksheetFunction.Max(Sheets("Employee").Range("$A$3:$C$" & EmpRecCount), Application.WorksheetFunction.Match(Sheets("Mpro").Range("A" & i), Sheets("Employee").Range("$B$3:$B$" & EmpRecCount), 0), 3)
DaysInProgress = EndDate - StartDate
Workbooks(WBName).Worksheets(WSName).Cells(i, 9) = StartDate
Workbooks(WBName).Worksheets(WSName).Cells(i, 11) = DaysInProgress
Workbooks(WBName).Worksheets(WSName).Cells(i, 10) = EndDate
Next
I require to get the MAX value from a Column where the Mpro column matches and then place in another sheet where the mpro matches :)
EMPLOYEE SHEET
MPRO | DATE(column C/3)
15 | 41157
15 | 41159
23 | 52222
10 | 26000
10 | 26001
MPRO SHEET
MPRO | EndDate | DaysInProgress
15 | |
23 | |
10 | |
WHat I would like in the Days in Progress and EndDate Fields are
MPRO | EndDate | DaysInProgress
15 | 41159 | 2
23 | 52222 | 0
10 | 26001 | 1
EndDate = Application.WorksheetFunction.Max(Sheets("Employee").Range("$A$3:$C$" & EmpRecCount), Application.WorksheetFunction.Match(Sheets("Mpro").Range("A" & i), Sheets("Employee").Range("$B$3:$B$" & EmpRecCount), 0), 3)
DaysInProgress = EndDate - StartDate
Workbooks(WBName).Worksheets(WSName).Cells(i, 9) = StartDate
Workbooks(WBName).Worksheets(WSName).Cells(i, 11) = DaysInProgress
Workbooks(WBName).Worksheets(WSName).Cells(i, 10) = EndDate
Next
I require to get the MAX value from a Column where the Mpro column matches and then place in another sheet where the mpro matches :)
EMPLOYEE SHEET
MPRO | DATE(column C/3)
15 | 41157
15 | 41159
23 | 52222
10 | 26000
10 | 26001
MPRO SHEET
MPRO | EndDate | DaysInProgress
15 | |
23 | |
10 | |
WHat I would like in the Days in Progress and EndDate Fields are
MPRO | EndDate | DaysInProgress
15 | 41159 | 2
23 | 52222 | 0
10 | 26001 | 1