• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help getting SELECT CASE subroutine to test every row

bufo0892

New Member
I have the following subroutine, which works to test cell C1 for a value & write a more descriptive value in D1. But I haven't been able to figure out how to loop it so that it will test column C in every row of the worksheet. Any help with this is much appreciated.

[pre]
Code:
Sub LongItemNameForAssets()
Select Case Range("C2").Text
Case "19"
inch = Chr(34)
Range("D2").Value = "HP 19" & inch & " CRT Monitor"
Case "17"
inch = Chr(34)
Range("D2").Value = "HP 17" & inch & " CRT Monitor"
Case "17 Flat Panel Monitor"
inch = Chr(34)
Range("D2").Value = "HP 17" & inch & " Flat Panel Monitor"
Case "19 Flat Panel Monitor"
inch = Chr(34)
Range("D2").Value = "HP 19" & inch & " Flat Panel Monitor"
Case "15"
inch = Chr(34)
Range("D2").Value = "HP 15" & inch & " CRT Monitor"
Case "20 Flat Panel Monitor"
inch = Chr(34)
Range("D2").Value = "HP 20" & inch & " Flat Panel Monitor"
Case "2710p"
Range("D2").Value = "HP 2710p Laptop"
Case "2730p"
Range("D2").Value = "HP 2730p Laptop"
Case "2700 Expansion Base"
Range("D2").Value = "HP 2700 Expansion Base"
End Select
End Sub
[/pre]
 
Bufo0892


The following will do what you want,

But please read the section at the end as well


====================

[pre]
Code:
Sub LongItemNameForAssets()

inch = Chr(34) 'Note that this only needs to be declared once and so is outside of the loop

For Each c In Range("C2:C100") 'Change C100 to suit

Select Case c.Text
Case "19"
Cells(c.Row, 4).Value = "HP 19" + inch + " CRT Monitor"
Case "17"
Cells(c.Row, 4).Value = "HP 17" + inch + " CRT Monitor"
Case "17 Flat Panel Monitor"
Cells(c.Row, 4).Value = "HP 17" + inch + " Flat Panel Monitor"
Case "19 Flat Panel Monitor"
Cells(c.Row, 4).Value = "HP 19" + inch + " Flat Panel Monitor"
Case "15"
Cells(c.Row, 4).Value = "HP 15" + inch + " CRT Monitor"
Case "20 Flat Panel Monitor"
Cells(c.Row, 4).Value = "HP 20" + inch + " Flat Panel Monitor"
Case "2710p"
Cells(c.Row, 4).Value = "HP 2710p Laptop"
Case "2730p"
Cells(c.Row, 4).Value = "HP 2730p Laptop"
Case "2700 Expansion Base"
Cells(c.Row, 4).Value = "HP 2700 Expansion Base"
End Select

Next
End Sub
[/pre]

==============


Did you think about setting up a table within Excel and then using formulas rather than a Macro to populate Column D


If you setup a Table in H4:I12 with 2 columns the first being the simple name as in Column C and the second being the full name as in Column D

The following will lookup the simple value from Column c and put it in Column D

Insert the following formula in D2 and copy down


=INDEX($H$4:$I$12,MATCH(C2,$H$4:$H$12,0),2)
 
Hui,

Thanks so much for your help with this. I appreciate that you not only help a novice solve a problem, but also suggest what might be a better way. I'll do it both ways for the experience, but let me ask you: what might be the pros & cons to each method?
 
Bufo


For what you are doing I would use the spreadsheet formulas, it is more transparent, can easily be updated as products change, are added or deleted.


The macro way is less flexible, and needs to be maintained by someone with knowledge of VBA as you change/add/delete products. It can be made flexible, but that adds to the complexity of what you are trying to achieve. It also needs to be ran everytime some one changes the products list, although that can be made automatic as well.


Macros are great at doing 2 main things,

1. Repeated functions where you have a series of steps which you need to repeat regularly

2. Developing complex functions that would be difficult in even a dozen helper rows/columns


The great thing about Excel is that there is no right or wrong way to do anything and the best way, is the way you know, that gets you the result you want, now.
 
Hui,

Thanks for your input, makes sense. I will use formulas in the sheet, much easier to maintain. Glad I had an opportunity to learn about the CASE structure though & most grateful for you showing me how to include it inside a loop.
 
Back
Top