使用 vba 宏使用两个搜索词(多次查找)搜索 excel [英] Searching an excel with two search words (multiple lookup) using vba macro

问题描述

我正在使用 vba 宏.
任务:
1)用户将提供两个输入即.售出的设备数量(B 列)和周数(A 列).
2) 用这两个搜索excel文件，得到收益.

(条件:
1.我们不需要修改excel文件中的任何内容.
2.我们必须先搜索devices sells然后我们可以去week number.)

注意:实际上我有一个不同的问题，这是对此的概括.

我使用了 find 函数，但我不明白它为什么起作用.

请告诉我要做什么.


考虑输入是 11 和 week 3 (所以搜索 11 然后检查它是否对应于 week 3与否.如果没有，请转到下一个.我试过了，但最终陷入了无限循环.)

I am using vba macros.
Task:
1) User will give two inputs viz. number of devices sold (column B) and week number (column A).
2) Search the excel file using these two and get the earnings.

(Conditions:
1. We dont have to modify anything in the excel file.
2.And we have to first search devices sold and then we can go to week number.)

Note: Actually i have a different problem, this is generalization for that.

I used find function but I dont understand why its working.

Please tell what is to be done.


Consider the input is 11 and week 3 (so search for 11 and then check if its corresponds to week 3 or not. If not, move to next. I tried this but ended up in an infinite loop.)

推荐答案

您正在寻找具有多个条件的查找.快速搜索提供了几个有用的链接，如下所列.已回答的我已标*号.我已经过滤掉了一些实际处理其他问题的链接.

You are looking for a Lookup with multiple criteria. A quick search gives several useful links, listed below. I have marked with * those that I have answered. I have filtered out some of the links that actually deal with other issues.

如链接所示，有多种选择可以实现您的目标.我个人更喜欢那些不需要数组公式的，使用使用LOOKUP<回答问题1/code> 或 使用 SUMPRODUCT 回答问题 2.

As seen in the links, there are several options to achieve your goal. I personally prefer those that do not need array formulas, using an answer to question 1 with LOOKUP or an answer to question 2 with SUMPRODUCT.

既然你提到你需要一个 VBA 解决方案，你可以使用 WorksheetFunction 在 VBA 代码中使用 Excel 公式，或者检查问题 7 的答案.

Since you mentioned that you need a VBA solution, you can use WorksheetFunction to use an Excel formula in VBA code, or check answers to question 7.

1. 多列与多列查找 *

Excel 中基于多列的查找 *

查找数据库(多个条件)

使用多个查询进行 Excel 查找

多个 VLOOKUP LOOKUP IF 语句?

如果三列匹配，则返回一个值优秀 *

VLookup 多列

比较一张 Excel 表格中的两列，与另一张表格中的两列，如果匹配，则从另一列复制数据 *

具有 2 个垂直标准的 vlookup 宏 *

http://spreadsheets.about.com/b/2014/01/08/excel-lookup-formula-using-multiple-criteria.htm

http://fiveminutelessons.com/learn-microsoft-excel/use-index-lookup-multiple-values-list

http://blogs.office.com/2012/04/26/using-multiple-criteria-in-excel-lookup-formulas/

http://www.xl-central.com/查找多重标准.html

http://www.get-digital-help.com/2012/05/22/lookup-multiple-values-in-different-columns-and-return-a-single-值/

PS:上面提供的(可能是)重复问题的(可能是部分)列表显示了https://meta.stackexchange.com/questions/211366/group-duplicate-questions-for-convenience

PS: The (probably partial) list of (probably) duplicated questions provided above shows the usefulness of what was proposed in https://meta.stackexchange.com/questions/211366/group-duplicate-questions-for-convenience

这篇关于使用 vba 宏使用两个搜索词(多次查找)搜索 excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！