More elegant way to write this?

[omnivl]

Is there a more elegant way to write this vba code?

    'price
    Select Case Sheets("control").Range("B4").Value
    Case 1: sentPrice1 = "most competitive"
    
    Case 2:
            Select Case masterWorksheet.Range("X47").Value
                Case 1: sentPrice1 = "most competitive"
                Case 2: sentPrice1 = "least competitive"
            End Select
            
            Select Case masterWorksheet.Range("X48").Value
                Case 1: sentPrice2 = "most competitive"
                Case 2: sentPrice2 = "least competitive"
            End Select
            
            Select Case masterWorksheet.Range("X49").Value
                Case 1: sentPrice3 = "most competitive"
                Case 2: sentPrice3 = "least competitive"
            End Select
 
    Case 3:
            Select Case masterWorksheet.Range("X47").Value
                Case 1: sentPrice1 = "most competitive"
                Case 2: sentPrice1 = "second most competitive"
                Case 3: sentPrice1 = "least competitive"
            End Select
            
            Select Case masterWorksheet.Range("X48").Value
                Case 1: sentPrice2 = "most competitive"
                Case 2: sentPrice2 = "second most competitive"
                Case 3: sentPrice2 = "least competitive"
            End Select
            
            Select Case masterWorksheet.Range("X49").Value
                Case 1: sentPrice3 = "most competitive"
                Case 2: sentPrice3 = "second most competitive"
                Case 3: sentPrice3 = "least competitive"
            End Select
            
    Case 4:
            Select Case masterWorksheet.Range("X47").Value
                Case 1: sentPrice1 = "most competitive"
                Case 2: sentPrice1 = "second most competitive"
                Case 3: sentPrice1 = "second least competitive"
                Case 4: sentPrice1 = "least competitive"
            End Select
            
            Select Case masterWorksheet.Range("X48").Value
                Case 1: sentPrice2 = "most competitive"
                Case 2: sentPrice2 = "second most competitive"
                Case 3: sentPrice2 = "second least competitive"
                Case 4: sentPrice2 = "least competitive"
            End Select
            
            Select Case masterWorksheet.Range("X49").Value
                Case 1: sentPrice3 = "most competitive"
                Case 2: sentPrice3 = "second most competitive"
                Case 3: sentPrice3 = "second least competitive"
                Case 4: sentPrice3 = "least competitive"
            End Select
            
    Case 5:
            Select Case masterWorksheet.Range("X47").Value
                Case 1: sentPrice1 = "most competitive"
                Case 2: sentPrice1 = "second most competitive"
                Case 3: sentPrice1 = "third most competitive"
                Case 4: sentPrice1 = "second least competitive"
                Case 5: sentPrice1 = "least competitive"
            End Select
            
            Select Case masterWorksheet.Range("X48").Value
                Case 1: sentPrice2 = "most competitive"
                Case 2: sentPrice2 = "second most competitive"
                Case 3: sentPrice2 = "third most competitive"
                Case 4: sentPrice2 = "second least competitive"
                Case 5: sentPrice2 = "least competitive"
            End Select
            
            Select Case masterWorksheet.Range("X49").Value
                Case 1: sentPrice3 = "most competitive"
                Case 2: sentPrice3 = "second most competitive"
                Case 3: sentPrice3 = "third most competitive"
                Case 4: sentPrice3 = "second least competitive"
                Case 5: sentPrice3 = "least competitive"
            End Select
            
   Case Else:
End Select

 

[Eric W]

Elegance is often in the eye of the beholder. Elegance is often taken to mean "Shorter", but if the shorter version is not understandable to the next guy who comes along and has to maintain it, then it's just a mess. After a programming career than spans decades, I've had many times when I've had a beautiful, short, efficient piece of code, where I've admired it, then ripped it out because I knew no one would understand it but me.

Your code may be long, but it's very clear as to what it's doing. Nevertheless, here's one way to write it shorter:

Sub test1()
Dim terms(2 To 5) As Variant
    
    terms(2) = Array("", "most ", "least ")
    terms(3) = Array("", "most ", "second most ", "least ")
    terms(4) = Array("", "most ", "second most ", "second least ", "least ")
    terms(5) = Array("", "most ", "second most ", "third most ", "second least ", "least ")
    
    b4 = Sheets("Control").Range("B4").Value
    sentprice1 = "most competitive"
    On Error Resume Next
    x47 = masterWorksheet.Range("X47").Value
    x48 = masterWorksheet.Range("X48").Value
    x49 = masterWorksheet.Range("X49").Value
    sentprice1 = terms(b4)(x47) & "competitive"
    sentprice2 = terms(b4)(x48) & "competitive"
    sentprice3 = terms(b4)(x49) & "competitive"
    
End Sub

It wouldn't surprise me if someone else came up with a still more "elegant" version, but you're the final arbiter, and in my book maintainability trumps elegance any day.