PDA

View Full Version : Solved: Use Index, Match, and Max VBA



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

Doug Robbins
07-04-2013, 02:25 AM
Assuming that there is a maximum of two rows for each MPRO and that the dates for each MPRO are in ascending order, the following should do what you want:

Dim i As Long, MPRO As Long, j As Long, StartDate As Long, EndDate As Long
Dim rngMPRO As Range
Set rngMPRO = Sheets("MPRO").Range("A1")
With rngMPRO
For i = 1 To .CurrentRegion.Rows.Count - 1
MPRO = .Offset(i, 0)
With Sheets("EMPLOYEE").Range("A1")
For j = 1 To .CurrentRegion.Rows.Count
If .Offset(j, 0) = MPRO Then
StartDate = .Offset(j, 1)
If .Offset(j + 1, 0) = MPRO Then
EndDate = .Offset(j + 1, 1)
rngMPRO.Offset(i, 1) = EndDate
rngMPRO.Offset(i, 2) = EndDate - StartDate
j = j + 1
Else
rngMPRO.Offset(i, 1) = StartDate
rngMPRO.Offset(i, 2) = 0
End If
End If
Next j
End With
Next i
End With