Excel VBA查找全部 [英] Excel VBA Find All
问题描述
我是在Sheet Test1 上运行一段while循环，以便将数据复制到Sheet Test2 上的特定行，其中参数匹配，但是我目前正在这样做的方式是循环遍历Sheet 2的每一行 - 两张数据都超过50,000行长，所以尽管我的方法对我的10行测试是完美的，但需要花费几个小时的时间为完整的数据。我想知道有人可以简化我的代码，理想情况下可以搜索匹配的行，而不是比较每个代码？
我尝试使用找到，但我无法让它处理多个参数。
这是我当前的代码，我省略了设置参数设置以节省空间，但它们存在于我的工作表中：
while1 = = lastRow1
Param1 = Sheets（Test1）。Cells.Range（A& row1）.Value
Param2 = Sheets（Test1）。Cells.Range（B& row1）.Value
Param3 = Sheets（Test1）。Cells.Range（D& row1）.Value
Param4 = Sheets（Test1）。Cells.Range（E& row1）.Value
Param5 = Sheets（Test1）。Cells.Range（F& row1）.Value
Cell_to_copy = Sheets（Test1）。Cells.Range（G& row1）。价值
*****这是我想要优化的比特************
对于row2 = 2到lastRow2
KParam1 = Sheets（Tes t2）。Cells.Range（A& row2）.Value
KParam2 = Sheets（Test2）。Cells.Range（B& row2）.Value
KParam3 = Sheets（Test2）。Cells.Range（I & row2）.Value
KParam5 = Sheets（Test2）。Cells.Range（G& row2）.Value
If（InStr（1，KParam1，Param1）> 0） _
And（（InStr（1，KParam2，Param2）> 0）Or（InStr（1，Param2，KParam2）> 0））_
And（（InStr（1，KParam3，Param3 ）> 0）或（InStr（1，KParam3，Param4）> 0））_
And（InStr（1，KParam5，Param5）> 0）_
然后
（Test2）。Cells.Range（L& row2）.Value = Cell_to_copy
End If
下一行2
************* ***************************************
row1 = row1 + 1
Wend
我想更改中间部分，而是执行搜索功能来查找行匹配如果查询，则d然后将 Cell_to_copy 复制到该行的单元格L。
这可能吗？如果绝对有必要，我可以将 Test2 中的可搜索值连接到一个单元格中，但是如果可以避免的话，我宁愿不能匹配一些假阳性。 / p>
提前感谢
Joe
OK，所以规则似乎是，如果
Test1.column A Test2.column A
Test1.column B位于Test2.column内部B
Test1.column D位于Test2.column内部I
Test1.column E位于Test2.column I $中b $ b Test1.column F在Test2.column里面找到G
then copy Test1.column G to Test2.Column L
是对吗？
为什么你不能在Sheet2上使用公式？
也许：Test2.Column L formula = = IF（FIND（A：A，Sheet1！A：A）> 0，Sheet1！G：G）
需要一个 OR 其他条件让它检查其他列 - 将在午饭后尝试！
如果没有，那么好的，这里我想我们需要考虑使用ADO和处理工作表一个数据库表。
如果您显示您的源数据的外观，以及您需要的结果，我可以为您尝试并编写一个示例，但是最好的选项是让您使用以下链接学习钓鱼。
看到这里： MSDN KB：如何使用ADO与Visual Basic或VBA中的Excel数据
并阅读：将Excel作为关系数据源对 Excel用户MVP网站
基本上任务是将所需的过滤数据读入ADO Recordset，然后使用CopyFromRecordset方法将结果转储到Sheet2中一步
所以你有一些阅读要做，让我们知道你是否需要一个例子或更多的帮助
Philip
As usual, I've wasted hours working on a problem before finally giving in and asking for help, so any help would be greatly appreciated!
I'm running a while loop on Sheet Test1 of my data in order to copy data to a specific row on Sheet Test2 where parameters match, but the way I'm currently doing this is by also looping through every row of Sheet 2 - both sheets of data are over 50,000 lines long, so although my method works perfectly for my 10 row tests it is taking several hours to work for the full data. I was wondering if someone could simplify my code, ideally so that it could search for matching rows rather than comparing each one?
I have tried using Find but I couldn't get it to work with multiple parameters.
This is my current code, I've omitted the part where I set the parameter settings to save space but they are present in my Worksheet:
While row1 <= lastRow1
Param1 = Sheets("Test1").Cells.Range("A" & row1).Value
Param2 = Sheets("Test1").Cells.Range("B" & row1).Value
Param3 = Sheets("Test1").Cells.Range("D" & row1).Value
Param4 = Sheets("Test1").Cells.Range("E" & row1).Value
Param5 = Sheets("Test1").Cells.Range("F" & row1).Value
Cell_to_copy = Sheets("Test1").Cells.Range("G" & row1).Value
*****THIS IS THE BIT I WANT TO OPTIMISE************
For row2 = 2 To lastRow2
KParam1 = Sheets("Test2").Cells.Range("A" & row2).Value
KParam2 = Sheets("Test2").Cells.Range("B" & row2).Value
KParam3 = Sheets("Test2").Cells.Range("I" & row2).Value
KParam5 = Sheets("Test2").Cells.Range("G" & row2).Value
If (InStr(1, KParam1, Param1) > 0) _
And ((InStr(1, KParam2, Param2) > 0) Or (InStr(1, Param2, KParam2) > 0)) _
And ((InStr(1, KParam3, Param3) > 0) Or (InStr(1, KParam3, Param4) > 0)) _
And (InStr(1, KParam5, Param5) > 0) _
Then
Sheets("Test2").Cells.Range("L" & row2).Value = Cell_to_copy
End If
Next row2
****************************************************
row1 = row1 + 1
Wend
I'd like to change the middle section to instead perform a search function to locate rows that match the If query, and then copy the Cell_to_copy to Cell L on that row.
Is this possible? I can concatenate the searchable values from Test2 into a single cell if it's absolutely necessary, but I'd rather not if it can be avoided as it will match some false positives.
Thanks in advance,
Joe
ok, so the rule seems to be, if
Test1.column A is found inside Test2.column A
Test1.column B is found inside Test2.column B
Test1.column D is found inside Test2.column I
Test1.column E is found inside Test2.column I
Test1.column F is found inside Test2.column G
then copy Test1.column G to Test2.Column L
is that right?
Why can't you do that using a formula on Sheet2?
maybe: Test2.Column L formula = =IF(FIND(A:A,Sheet1!A:A)>0,Sheet1!G:G)
that formula needs an OR with the other conditions to make it check the other columns - will try after lunch!
if not, then ok, here I think we need to consider using ADO and treating the worksheet as a db table.
If you show what your source data looks like, and the result you need, I can try and write a sample for you, but by far the best option is for you to learn to fish using the below links.
see here for this: MSDN KB: How To Use ADO with Excel Data from Visual Basic or VBA
and also read: Treat Excel As a Relational Data Source on the Excel User MVP website
and also read: Office Space: Using ADO to Query an Excel Spreadsheet
basically the task is to read the filtered data that you need into an ADO Recordset, then use CopyFromRecordset method to dump the results into Sheet2 in one step
So you have some reading to do, let us know if you need an example or more help
Philip
这篇关于Excel VBA查找全部的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
