Hi all expertise,
I have a file summary effort & performance rate of all staffs in company in many project and many months.
This file conclude many sheet- 1 sheet is 1 project:
Each sheet have data like below:
Jan 2016 Feb 2016 Mar 2016
ID Name Level Group Role Total Project ID effort Peformance Project ID Effort Peformance Project ID Effort Peformance
AAA001 Vo Trong Nhan STA Tech TL 91.65 NGR_2016-01 15.81 Good NGR_2016-02 8.4 Good NGR_2016-03 20.63 Good
And in sheet summary of each month (ex: 2016-01): I have ID & Name of all staffs in company. I want to vlookup each Employee ID in multi sheets to in put Project ID, Effort, performance in this month.
However, 1 Staff can do 2,3 project so when I use VBA Multi sheet vlookup, it just return result it found first and exit function.
I want when vlookup a result in a sheet, it still vlookup other sheets and if find another result, it will insert a row, copy ID, Name, Level, Group of this employee and fill the 2nd result in this row.
Like that:
Jan-16
ID Name Level Group Quit Role Total effort Project ID Effort (m-d) Peformance Count
AAA002 Nguyen Thi Thanh Tam SrPM Grp#1 TL 12 NGR_2016-01 12 Good
AAA002 Nguyen Thi Thanh Tam SrPM Grp#1 TL 27 PCS_2016-01 15 Normal
My VBA Code:
Function MultiShtVlookup(rngLup As Range, rngArry As Range, ColIdx As Long, bolExact As Boolean)
Dim ws As Worksheet
Dim varTemp As Variant
For Each ws In Worksheets
'Following line prevents searching in workheet containing the formula
If ws.Name <> Application.Caller.Parent.Name Then
With ws
varTemp = Null
varTemp = Application.VLookup(rngLup.Value, .Range(rngArry.Address(1, 1)), ColIdx, bolExact)
If Not IsError(varTemp) Then
MultiShtVlookup = varTemp
Exit Function
End If
End With
End If
Next ws
If IsError(varTemp) Then
MultiShtVlookup = CVErr(varTemp) 'Display the standard not found error (#N/A)
End If
End Function
Thank you very much.
Bookmarks