+ Reply to Thread
Results 1 to 2 of 2

[HELP] MultiVlookup & Insert row when vlookup have 2 results

  1. #1
    Registered User
    Join Date
    08-26-2016
    Location
    Viet Nam
    MS-Off Ver
    2010
    Posts
    1

    [HELP] MultiVlookup & Insert row when vlookup have 2 results

    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.

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: [HELP] MultiVlookup & Insert row when vlookup have 2 results

    3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the [#] button at the top of the post window (if you are editing an existing post, press Go Advanced to see the [#] button). The result will appear like this in the post window:

    Please Login or Register  to view this content.
    ... and appear like this when posted:



    copy to clipboard
    your code here ...
    and here ...
    and here
    You can also type the code tags in manually if you prefer. For more information about these and other tags, click here.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] using Vlookup with multiple results and averaging the results.
    By xatomicx in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-12-2015, 10:19 AM
  2. Using vLookup based on results from a vLookup & returning an undetermined list
    By NormalityBan in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 08-14-2014, 05:02 AM
  3. Function Multivlookup deletes duplicates...
    By aucho in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-08-2014, 05:26 AM
  4. [SOLVED] Using VLookup but results show formula instead of results...
    By excel me in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 11-28-2013, 09:40 PM
  5. [SOLVED] Vlookup: need to insert multiple non- numerical results in one cell
    By ipausa in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-28-2013, 02:43 PM
  6. VLookup that Results with False or Blank Results
    By mycon73 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-12-2013, 07:16 PM
  7. [SOLVED] Macro to vlookup and insert results in initial with brackets
    By cathchin in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-13-2013, 08:40 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1