Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

excel - VBA - Match Lookup With Multiple Parameters

I have a few macros that perform various tasks, and it seems rather pointless that the final step of the process requires the user to manually enter the formula, so here is the scenario:

I have 2 spreadsheets:

  1. Consolidated Sheet - Contains an employee number and the employee details (See image below)

    enter image description here

  2. Summary Sheet - Contains only 3 columns; AppName, EmpNum, Status (See image below)

    Note: There is currently no data on any of these sheets, and on the summary sheet, I create a table from the data so that the formula will work

    enter image description here

On the consolidated sheet at the end of the column "Supervisor Email", there are a few more columns containing the application names (See image below)

enter image description here

Originally, the idea was to lookup the employee number and app name with the below formula, then copy the status to the relevant cell - but this formula does not seem to be working AND it requires the user to insert the formula manually into each cell under every application name.

Formula:

=IFERROR(INDEX(Table2[Status],SUMPRODUCT((Table2[App Name]=L$1)*(Table2[Employee Number]=$A2)*(ROW(Table2[Status])))-1,1),"")

Now, what I am looking to do is relatively the same thing, but with 2 differences:

  1. I don't want the status to show in the cell the formula is placed into - If there's a match, then it should display "MATCH"

    and if there is no match then the cell should be blank

  2. I need this formula to be in a macro that finds the "lastRow" and populates across the columns from App1 - to the last AppName and down to the "lastRow"

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

I got this question answered here by user Subodh Tiwari (Neeraj):

https://www.experts-exchange.com/questions/29098511/VBA-VLOOKUP-With-Multiple-Parameters.html#acceptAnswerByMember

Sample workbook is attached with the post in this link.

Here is the complete code:

Sub PlaceFormula()
Dim ws As Worksheet
Dim lr As Long
Dim lc As Long

With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
    .EnableEvents = False
End With

Set ws = Sheets("Main")
lr = ws.Cells(Rows.Count, 1).End(xlUp).Row
lc = ws.Cells(1, Columns.Count).End(xlToLeft).Column

ws.Range("B2", ws.Cells(lr, lc)).Formula = "=IF(ISNUMBER(MATCH($A2&B$1,INDEX(tStatus[[Employee Number]:[Employee Number]]&tStatus[[Wk Number]:[Wk Number]],),0)),""Match"","""")"
ws.Range("B2", ws.Cells(lr, lc)).Value = ws.Range("B2", ws.Cells(lr, lc)).Value

With Application
    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
    .EnableEvents = True
End With
End Sub

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...