Excel 2007 VBA If Then Statement Using Multiple Criteria

Question

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:

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!

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

Answer 1

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

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

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

The A3 in the first part of the VLOOKUP is the cell reference to the Cust_Num in the row.

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

2 will direct the result of the VLOOKUP to the second column, in this case the Company Name that you manually entered.

And FALSE is so that it will only return exact matches.

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_Num in the list you can do the following:

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

Then In the Window:

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

Then you can just fill in the corresponding Company Names one time with these values and use the VLOOKUP as described earlier.

If The Find Method Is Prefered You Can use this:

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

Answer 2

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.

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

Answer 3

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.)

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