使用链接到带有计数器的FOR循环的多个IF语句设置变量

本文介绍了使用链接到带有计数器的FOR循环的多个IF语句设置变量的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有3张纸。在管理器工作表中，有7个条件下拉列表： H5 ， H7 ， H9 ， H11 ， H13 ， H15 ， H17 。选择条件后，用户单击复制按钮，宏将在工作表的数据列 A：G 中搜索与所选条件匹配的行。然后，它为匹配的行复制范围 P：W 并将其粘贴到从第11行开始的工作表 Quote中。重要的是要注意用户未选择准则，则该准则将被忽略（请参见代码中的 VbNullString ）

I have 3 sheets. In the sheet "Manager", there are 7 dropdown lists for criteria: H5, H7, H9, H11, H13, H15, H17. Once the criteria are selected and the user clicks on the button "COPY", the macro searches in the sheet "Data" columns A:G the rows matching the selected criteria. Then it copies the range P:W for the matching rows and pastes it in sheet "Quote" starting from row 11. It is important to note when the user does not select a criterion for any of the dropdown list, then that criterion is just ignored (see VbNullString in the code)

现在，该宏运行良好，可以为公司下拉列表选择多个条件（ H5 ），为其他条件选择单个条件（ H7 ， H9 ， H11 ， H13 ， H15 ， H17 ）。

By now, the macro runs fine with multiple criteria selection for the Company dropdown list (H5) and single criterion selection for the others (H7, H9, H11, H13, H15, H17).

Sub Quote() Dim Source As Worksheet Dim Target As Worksheet Dim Manager As Worksheet Dim Multiple () As String 'Here Dim InfoA As String Dim InfoB As String Dim InfoC As String Dim ProductType As String Dim SalesStatus As String Dim finalrow As Integer Dim counter As Integer Dim I As Integer Set Source = Worksheets("Data") Set Target = Worksheets("Quote") Set Manager = Worksheets("Manager") If Worksheets("Manager").Range("H5").Value <> vbNullString Then 'Here Multiple = Split(Worksheets("Manager").Range("H5").Value, ",") 'Here If Worksheets("Manager").Range("H13").Value <> vbNullString Then 'Modified Multiple = Split(Worksheets("Manager").Range("H13").Value, ",") 'Here Else 'Here Multiple = Split("", "") 'Here End If 'Here End If 'Here InfoA = Worksheets("Manager").Range("H7").Value InfoB = Worksheets("Manager").Range("H9").Value InfoC = Worksheets("Manager").Range("H11").Value ProductType = Worksheets("Manager").Range("H15").Value SalesStatus = Worksheets("Manager").Range("H17").Value finalrow = Source.Cells(Rows.Count, 1).End(xlUp).Row For counter = 0 To UBound(Multiple) 'Here lookupMult = Trim(Multiple(counter)) 'Here For I = 2 To finalrow thisComp = Source.Cells(I, 1) thisInfA = Source.Cells(I, 2) thisInfB = Source.Cells(I, 3) thisInfC = Source.Cells(I, 4) thisProd = Source.Cells(I, 5) thisType = Source.Cells(I, 6) thisSale = Source.Cells(I, 7) If (thisComp = lookupMult Or lookupMult = vbNullString) Then 'Here If (thisInfA = InfoA Or InfoA = vbNullString) Then If (thisInfB = InfoB Or InfoB = vbNullString) Then If (thisInfC = InfoC Or InfoC = vbNullString) Then If (thisProd = lookupMult Or lookupMult = vbNullString) Then 'Here If (thisType = ProductType Or ProductType = vbNullString) Then If (thisSale = SalesStatus Or SalesStatus = vbNullString) Then Source.Range(Source.Cells(I, 16), Source.Cells(I, 23)).Copy Target.Range("A200").End(xlUp).Offset(1, 0).Resize(1, 8) End If End If End If End If End If End If End If Next I Next counter End Sub

除了多条件选择对于 H5 ，我还需要为产品（ H13 ）启用它。为此，我尝试使用更详细的IF语句修改变量Company。在图片中，报价表是我应该得到的结果。但实际上没有任何内容可以复制粘贴，而且我无法弄清楚自己在做什么错。我在此处添加了一些注释，以显示我修改的代码的哪一部分。预先感谢您的指导。

In addition to the multiple criteria selection for H5, I need also to enable it for the Product (H13). To do so, I tried to modify the variable Company using a more elaborated IF statement. In the picture, the sheet "Quote" is the result I should get. But in fact nothing is copy-pasted and I cannot figure out what I'm doing wrong. I added some comments 'Here to show what part of the code I modified. By advance thanks for any guidance.

推荐答案

我找到了解决问题的方法。这不是灵丹妙药，但至少它可以正常工作。之后，如果有人知道在SQL查询和结构化表之外优化代码的方法，请随时分享，我将尝试。注意，我相信SQL查询可能是一个更好的选择，但是这意味着我必须重做几乎所有代码并使用尚不知道的方法。

I found a way to solve my issue. It is not a silver bullet, but at least it works as it should. After, if anyone knows some way to optimize the code, outside of SQL queries and structured tables, feel free to share and I will try. Note I believe SQL queries is probably a better option, but it means I have to rework almost all my code and use methods I do not know (yet). I will study it later for a future update.

问题是计数器一词可能是保留变量。因此，我无权在共享相似功能的循环中添加另一个FOR。由于我已按字母更改了计数器变量，因此我现在可以为其他下拉列表选择多个条件。在下面的示例中，我只是针对H5和H13使其清晰可见。

The problem is the word "counter" might be a reserved variable. So, I was not authorized to add another FOR in my loop sharing similar features. Since I changed the "counter" variable by letters, I'm now able to do multiple criteria selection for other dropdown lists. In the example below, I just made it for H5 and H13 in order to keep it clear.

Sub Quote() Dim Source As Worksheet Dim Target As Worksheet Dim Manager As Worksheet Dim Company () As String Dim InfoA As String Dim InfoB As String Dim InfoC As String Dim Product () As String Dim ProductType As String Dim SalesStatus As String Dim finalrow As Integer Dim I As Integer Dim J As Integer Dim K As Integer Set Source = Worksheets("Data") Set Target = Worksheets("Quote") Set Manager = Worksheets("Manager") If Worksheets("Manager").Range("H5").Value <> vbNullString Then Company= Split(Worksheets("Manager").Range("H5").Value, ",") Else Company = Split("", "") End If InfoA = Worksheets("Manager").Range("H7").Value InfoB = Worksheets("Manager").Range("H9").Value InfoC = Worksheets("Manager").Range("H11").Value If Worksheets("Manager").Range("H13").Value <> vbNullString Then Product = Split(Worksheets("Manager").Range("H13").Value, ",") Else Product = Split("", "") End If ProductType = Worksheets("Manager").Range("H15").Value SalesStatus = Worksheets("Manager").Range("H17").Value finalrow = Source.Cells(Rows.Count, 1).End(xlUp).Row For K = 0 To UBound(Company) lookupComp = Trim(Company(K)) For J = 0 To UBound(Product) lookupProd = Trim(Product(J)) For I = 2 To finalrow thisComp = Source.Cells(I, 1) thisInfA = Source.Cells(I, 2) thisInfB = Source.Cells(I, 3) thisInfC = Source.Cells(I, 4) thisProd = Source.Cells(I, 5) thisType = Source.Cells(I, 6) thisSale = Source.Cells(I, 7) If (thisComp = lookupComp Or lookupComp = vbNullString) Then If (thisInfA = InfoA Or InfoA = vbNullString) Then If (thisInfB = InfoB Or InfoB = vbNullString) Then If (thisInfC = InfoC Or InfoC = vbNullString) Then If (thisProd = lookupProd Or lookupProd = vbNullString) Then If (thisType = ProductType Or ProductType = vbNullString) Then If (thisSale = SalesStatus Or SalesStatus = vbNullString) Then Source.Range(Source.Cells(I, 16), Source.Cells(I, 23)).Copy Target.Range("A200").End(xlUp).Offset(1, 0).Resize(1, 8) End If End If End If End If End If End If End If Next I Next J Next K End Sub