VBA Case Select based on Multiple Variables

user_14314515 0
Viewed 280

Trying to build a Select Case statement that does a similar job to a nested if function. Essentially I want to give a cell a value (in Column i + 1) based on values of two columns (column P and column I) and then apply it to a down the whole column of numbers down to the lastrow (hence lr variable) Currently, I am getting type mismatch error on the select case line.

  Dim i As Long
    Dim RowNum As Long
    Dim lr As Long

    Set i = 10
    RowNum = 2
    lr = Worksheets("1").cells.Find("*", cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
    Set tbl = ActiveSheet.ListObjects("1")
    
    Do Until cells(RowNum, i + 1) = lr
                    Select Case cells(RowNum, "P") And cells(RowNum, "I").Value
                    Case Is = "1" And "Available"
                            cells(RowNum, i + 1).Value = "Purchase"
                    Case Is = "2" And "Not Available"
                            cells(RowNum, i + 1).Value = "Attempt Purchase"
                    Case Is = "3"
                            cells(RowNum, i + 1).Value = "Purchase Automatic"
                    Case Is = "4" 
                            cells(RowNum, i + 1).Value = "Do not Purchase"
                    Case Else
                            cells(RowNum, i + 1).Value = "N/A"
    End Select
    RowNum = RowNum + 1
    Loop

Any help is greatly appreciated. Thanks

vba excel
2 Answers

Multi Select Case

  • Select Case needs one expression. Handle the other with an If statement.

Snippet

Select Case Cells(RowNum, "P")
    Case Is = "1"
        If Cells(RowNum, "I").Value = "Available" Then
            Cells(RowNum, i + 1).Value = "Purchase"
        End If
    Case Is = "2"
        If Cells(RowNum, "I").Value = "Not Available" Then
            Cells(RowNum, i + 1).Value = "Attempt Purchase"
        End If
    Case Is = "3"
            Cells(RowNum, i + 1).Value = "Purchase Automatic"
    Case Is = "4"
            Cells(RowNum, i + 1).Value = "Do not Purchase"
    Case Else
            Cells(RowNum, i + 1).Value = "N/A"
End Select
user_98140690

If you had read up on how the Select Case structure works you would realise that the way you are structuring your Select Case is not possible. In the VBA IDE put the cursor on Select and Press F1. This will bring up the help page of Select Case.

To achieve something similar to what you need to achieve you will need to concatenate the two values to give a single value and adjust the case values accordingly. The code below assumes that "CStr(Cells(RowNum, "I").Value" is equivalent to "" where necessary for the Case labels in the Case structure to work correctly.

You should also be aware that 'Set i' is incorrect as I is a value variable and not a reference variable. Use i=10.

Dim i As Long
Dim RowNum As Long
Dim lr As Long

i = 10
RowNum = 2
lr = Worksheets("1").Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
Set tbl = ActiveSheet.ListObjects("1")
    
Do Until Cells(RowNum, i + 1) = lr
    
    Select Case Trim$(CStr(Cells(RowNum, "P").Value) & CStr(Cells(RowNum, "I").Value))
                    
        Case "1Available"
        
            Cells(RowNum, i + 1).Value = "Purchase"
            
        Case Is = "2Not Available"
        
            Cells(RowNum, i + 1).Value = "Attempt Purchase"
            
        Case Is = "3"
        
            Cells(RowNum, i + 1).Value = "Purchase Automatic"
            
        Case Is = "4"
        
            Cells(RowNum, i + 1).Value = "Do not Purchase"
            
        Case Else
        
            Cells(RowNum, i + 1).Value = "N/A"
            
    End Select
    
    RowNum = RowNum + 1
    
Loop
user_71773460
Related