使用链接到带有计数器的FOR循环的多个IF语句设置变量 [英] Set up a variable with multiple IF statements linked to a FOR loop with a counter

问题描述

我有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  

这篇关于使用链接到带有计数器的FOR循环的多个IF语句设置变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！