Excel 2007 VBA If Then 使用多个标准的语句

Excel 2007 VBA If Then Statement Using Multiple Criteria

提问by Zachary Smith

I have a multiple IF criteria statement, of which if any are true should return the value specified. When I compile the code below it returns:

我有一个多 IF 条件语句，如果有的话应该返回指定的值。当我编译下面的代码时，它返回：

cust_num    company name

XX278   
XX004       Barco   
XX004   
XX278   
XX004   
XX004   
XX278   
XX278   

My intention is to have the cust_num "XX278" return "Barco" under company name as well since it matches condition2, but for some reason it's skipping that and doesn't appear to be looping through all of the rows as I intended (e.g. all XX004, XX278 cust_num should have company names). Why does my code not loop through each row? Any help would be greatly appreciated. Thanks!

我的意图是让 cust_num "XX278" 在公司名称下也返回 "Barco"，因为它与条件 2 匹配，但由于某种原因，它跳过了它并且似乎没有按照我的意图循环遍历所有行（例如所有XX004、XX278 cust_num 应该有公司名称）。为什么我的代码不循环遍历每一行？任何帮助将不胜感激。谢谢！

Dim v As Integer
Dim y As Integer

y = 0
condition1 = (ActiveSheet.Rows(1).Find("cust_num", LookAt:=xlPart).Offset(1 + y, 0) = "XX004")
condition2 = (ActiveSheet.Rows(1).Find("cust_num", LookAt:=xlPart).Offset(1 + y, 0) = "XX278")
condition3 = (ActiveSheet.Rows(1).Find("cust_num", LookAt:=xlPart).Offset(1 + y, 0) = "XX318")

v = ActiveSheet.Rows(1).Find("customer_name", LookAt:=xlPart).End(xlDown).Count

For y = 0 To v
If condition1 Or condition2 Or condition3 Then ActiveSheet.Rows(1).Find _
    ("company name",LookAt:=xlPart).Offset(1 + v, 0) = "Barco"
Next

回答by user2140261

Set up a Sheet that has all the Cust_Num's in a list with the corresponding Company Nameto its right. Like in the picture below:

设置一个表，其中包含Cust_Num列表中的所有Company Name，其右侧对应的是 。如下图所示：

Then once you have that all set use the VLOOKUPFunction from excel. This is my example formula:

然后，一旦您完成所有设置，请使用VLOOKUPexcel 中的函数。这是我的示例公式：

=VLOOKUP(A3,LookUp!$A:$B,2,FALSE)

The A3in the first part of the VLOOKUP is the cell reference to the Cust_Numin the row.

的A3在VLOOKUP的第一部分是单元参照Cust_Num所述排。

The LookUp!$A$1:$B$9Is the array the holds your look up values.

该LookUp!$A$1:$B$9是数组的握着你的查找值。

2will direct the result of the VLOOKUPto the second column, in this case the Company Namethat you manually entered.

2会将 的结果定向VLOOKUP到第二列，在本例中Company Name是您手动输入的。

And FALSEis so that it will only return exact matches.

并且FALSE它只会返回完全匹配。

Then I simply had to drag this formula down and I ended up with this:

然后我只需要把这个公式拖下来，我就得到了这个：

*NOTE: *If you need help getting a list of each Cust_Numin the list you can do the following:

*注意：*如果您需要帮助获取列表中的每个Cust_Num列表，您可以执行以下操作：

Select the list of all Cust_Num's and On the Data Tab, in the Ribbon, Select Advanced Filter:

选择所有的列表，Cust_Num然后在数据选项卡上的功能区中，选择高级过滤器：

Then In the Window:

然后在窗口中：

1. Select Copy To Another Location
2. Enter The Copy ToRange to be the Lookup Sheet
3. Make sure to check the check box for Unique Records Only

1. 选择 Copy To Another Location
2. 输入Copy To要作为查找表的范围
3. 确保选中复选框 Unique Records Only

Then you can just fill in the corresponding Company Namesone time with these values and use the VLOOKUPas described earlier.

然后你可以只填写相应的Company Names一次这些值并使用VLOOKUP前面描述的。

If The Find Method Is Prefered You Can use this:

如果首选 Find 方法，您可以使用此方法：

Sub AddAllNames()

Call AddCompanyNameNextToCust_Num("37004", "Varco")
Call AddCompanyNameNextToCust_Num("44278", "Varco")
Call AddCompanyNameNextToCust_Num("44318", "Varco")
Call AddCompanyNameNextToCust_Num("12345", "Name1")
Call AddCompanyNameNextToCust_Num("12344", "Name1")
Call AddCompanyNameNextToCust_Num("12346", "Name1")
Call AddCompanyNameNextToCust_Num("98765", "Name2")
Call AddCompanyNameNextToCust_Num("56789", "Name2")
Call AddCompanyNameNextToCust_Num("89756", "Name2")

End Sub


Function AddCompanyNameNextToCust_Num(strCust_Num As Variant, strCompanyName As String)

    Dim rngCust_nums As Range, rngFoundCell As Range, rngFirstCellFound As Range
    Dim rngCust_NumsColumn As Long
    Dim boolFinished As Boolean

    'Get Column With header "cust_num"
    rngCust_NumsColumn = WorksheetFunction.Match("cust_num", Rows(1), 0)

    'Set The Search range to column from last line
    Set rngCust_nums = ActiveSheet.Columns(rngCust_NumsColumn)


    'Get the first matching value of Cust_Num (passed into sub)
    Set rngFoundCell = rngCust_nums.Find(What:=strCust_Num, LookIn:=xlValues, _
                LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)

    'Check to make sure a match was found/ "Not Nothing"       
    If Not rngFoundCell Is Nothing Then

        'Save the First Found Cell 
        Set rngFirstCellFound = rngFoundCell

        'Add Company Name One Column to the Right of First Found Cust_Num
        rngFoundCell.Offset(, 1).Value = strCompanyName

        'Start Looping a "FindNext" 
        Do While boolFinished = False
            'Set each new match into an overwriting Variable
            Set rngFoundCell = rngCust_nums.FindNext(After:=rngFoundCell)

            'Make sure the match is "Something"
            If Not rngFoundCell Is Nothing Then
                'Make sure We have not gone through the whole list and that we
                'are not back to the begining
                If rngFoundCell.Address = rngFirstCellFound.Address Then Exit Do
                'If a new match is found and is not the starting point then add 
                'the company name in the next column
                rngFoundCell.Offset(, 1).Value = strCompanyName
            Else
                'When nothing is Found End loop
                boolFinished = True
            End If

        Loop

    Else 'If not even one match was found
        MsgBox strCust_Num & " not Found"
    End If


End Function

回答by PowerUser

It looks like your conditions are being evaluated before the loop even starts (i.e. only evaluated when y=0 and not during the loop.)

看起来您的条件甚至在循环开始之前就被评估了（即仅在 y=0 时评估而不是在循环期间评估。）

I see a few problems with your code, but you can try this (no guarantees, I can't test this right now):

我发现您的代码存在一些问题，但您可以尝试此操作（不能保证，我现在无法对此进行测试）：

Dim v As Integer
Dim y As Integer
Dim condition1 as boolean
Dim condition2 as boolean
Dim condition3 as boolean

y = 0

v = ActiveSheet.Rows(1).Find("customer_name", LookAt:=xlPart).End(xlDown).Count

For y = 0 To v
    condition1 = (ActiveSheet.Rows(1).Find("cust_num", LookAt:=xlPart).Offset(1 + y, 0) = "XX004")
    condition2 = (ActiveSheet.Rows(1).Find("cust_num", LookAt:=xlPart).Offset(1 + y, 0) = "XX278")
    condition3 = (ActiveSheet.Rows(1).Find("cust_num", LookAt:=xlPart).Offset(1 + y, 0) = "XX318")
    If condition1 Or condition2 Or condition3 Then ActiveSheet.Rows(1).Find _
        ("company name",LookAt:=xlPart).Offset(1 + v, 0) = "Barco"
Next

回答by Zachary Smith

Through the help of other postings I found an easier way to evaluate multiple criteria, instead of using an If Then statement, I used Select Case which I found to be much more efficient. The code below will look for the cust_num in a row, if the case is satisfied the company name will be inserted into the same row one column to the right.

通过其他帖子的帮助，我找到了一种更简单的方法来评估多个标准，而不是使用 If Then 语句，我使用了我发现效率更高的 Select Case。下面的代码将在一行中查找 cust_num，如果满足大小写，公司名称将被插入到同一行右侧一列。

Dim Nu As Range
Dim cmpny As Range
Dim v As Integer
Dim y As Integer

v = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row 'count number of rows


Set Nu = ActiveSheet.Rows(1).Find("cust_num", LookAt:=xlPart) 'set Nu = cust_num column header
Set cmpny = ActiveSheet.Rows(1).Find("company name", LookAt:=xlPart) 'set cmpny = company name column header

For y = 0 To v 'loop through each row

    Select Case Nu.Offset(1 + y, 0).Value 'row 1 + y of "cust_num"
        Case "XX004", "XX278", "XX318" 'if "cust_num" row = these #'s
            cmpny.Offset(1 + y, 0).Value = "Barco" 'Then corresponding row under "company name" column = "Varco"
Next