Select Case Function with Multiple Criteria is Not Looping

Question

I am new to vba and my first time working with "Select Case". Briefly, I am trying to loop (number of rows will change from sheet to sheet) by finding the "cust_num" column header and go through each row, if the cust_num matches criteria on a particular row then "Barco" will be put into the same row under the "company name" column.

When I compile "Barco" is placed under the first row only, so appears not to be looping through each row, sample below.

XX278        Barco
XX004   
XX004   
XX278   
XX004   
XX004   
XX278   
XX278   





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

    v = ActiveSheet.Rows(1).Find("customer_name", LookAt:=xlPart).End(xlDown).Count - 1 
'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

    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"
            End Select
    Next

Thanks @TimWilliams that fixed the error. When I run the macro it's putting "Barco" in the first row that matches the cust_num, but doesn't seem to be looping through any other rows. I tried to put the set ranges inside the For loop but that didn't seem to work. Any ideas? Thanks again! — Zachary Smith, Jun 24 '13 at 13:06
Sure, I have updated the question and included the current code. Thanks for taking a look, I really appreciate it. — Zachary Smith, Jun 24 '13 at 17:17
I don't see any obvious problems there: have you tried setting a breakpoint in the loop to check it's seeing the values you expect? It's possible that (eg) your company numbers have extra spaces or something along those lines. Add some `debug.print` statements so you can check your assumptions about (eg) the value of `v` — Tim Williams, Jun 24 '13 at 17:42
Great, thanks for the advice. The row count was not accurate, so just made a change and that fixed the issue. Now that I know how to debug that will likely eliminate my basic questions, hopefully:). Since I'm new to stackoverflow, can you advise on how I should finalize this question, ie mark it as answered? — Zachary Smith, Jun 24 '13 at 23:00
You can post an answer yourself and accept it: http://stackoverflow.com/help/self-answer — Tim Williams, Jun 24 '13 at 23:38

score 0 · Answer 1 · answered Jun 25 '13 at 13:06

With @TimWilliams help my code now works. This uses the Select Case function to loop through specified rows and determines if the criteria has been satisifed for that cell, if so a name is inserted on the same row to the right of the criteria cell (in this case the 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 
'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

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"
        End Select
Next

Select Case Function with Multiple Criteria is Not Looping

1 Answers1