More efficient way to write a sequential If - Else statement

[bioplz]

I am working on a program that contains a selection of a train number, and once that train number is selected there are two cars associated with each train number. These numbers will increase sequentially and the car numbers will also increase sequentially.

What I want to be able to do is write a smaller, more efficient, code that can continue without manually adding more code for hundreds of trains, and their associated cars.
Instead of writing an If-elseif-else statement for every train, could I write something that will manually track that?

The code provided is a snippet of what the final code will entail.

If txtTrain = "7" Then
        If Me.cbxCar = "A" Then
            carNumber = "7013"
        ElseIf Me.cbxCar = "B" Then
            carNumber = "7014"
        Else
            carNumber = "7013 - 7014"
        End If
    ElseIf txtTrain = "8" Then
        If Me.cbxCar = "A" Then
            carNumber = "7015"
        ElseIf Me.cbxCar = "B" Then
            carNumber = "7016"
        Else
            carNumber = "7015 - 7016"
        End If
    ElseIf txtTrain = "9" Then
        If Me.cbxCar = "A" Then
            carNumber = "7017"
        ElseIf Me.cbxCar = "B" Then
            carNumber = "7018"
        Else
            carNumber = "7017 - 7018"
        End If
    ElseIf txtTrain = "10" Then
        If Me.cbxCar = "A" Then
            carNumber = "7019"
        ElseIf Me.cbxCar = "B" Then
            carNumber = "7020"
        Else
            carNumber = "7019 - 7020"
        End If
    ElseIf txtTrain = "11" Then
        If Me.cbxCar = "A" Then
            carNumber = "7021"
        ElseIf Me.cbxCar = "B" Then
            carNumber = "7022"
        Else
            carNumber = "7021 - 7022"
        End If
    ElseIf txtTrain = "12" Then
        If Me.cbxCar = "A" Then
            carNumber = "7023"
        ElseIf Me.cbxCar = "B" Then
            carNumber = "7024"
        Else
            carNumber = "7023 - 7024"
        End If
    End If

 

[BSALV]

write all the combinations in a table of 3 columns somewhere in your workbook and use vlookup, index, ...
otherwise you can also use "select case"

 

[steve the fish]

Try and adapt this:

txtTrain = 10
cbxCar = "B"
myNum = (txtTrain - 7) * 2 + 7013


Select Case cbxCar
    Case "A"
        carNumber = myNum
    Case "B"
        carNumber = myNum + 1
    Case Else
        carNumber = myNum & "-" & myNum + 1
End Select

MsgBox carNumber

 

[bioplz]

Try and adapt this:

txtTrain = 10
cbxCar = "B"
myNum = (txtTrain - 7) * 2 + 7013


Select Case cbxCar
    Case "A"
        carNumber = myNum
    Case "B"
        carNumber = myNum + 1
    Case Else
        carNumber = myNum & "-" & myNum + 1
End Select

MsgBox carNumber

I used this code for my program and it seemed to be working, until I received feedback the results were always 70** - 70**. The case never selects only A or B. I have tried this code on a separate userform to test and it works properly. Is there any reason that it wouldn't work in my regular project?

    myNum = (txtTrain - 7) * 2 + 7013

    Select Case cbxCar
        Case "A"
            carNumber = myNum
        Case "B"
            carNumber = myNum + 1
        Case Else
            carNumber = myNum & "-" & myNum + 1
    End Select
        
    With Sheets("Data")
        .Cells(iCnt, 1) = iCnt - 2
        .Cells(iCnt, 2) = frmAddNewReport.txtStartDate
        .Cells(iCnt, 3) = CStr(txtTrain)
        .Cells(iCnt, 4) = CStr(carNumber)
        .Cells(iCnt, 5) = frmAddNewReport.cbxSupplier
        .Cells(iCnt, 6) = frmAddNewReport.txtIssues
        .Cells(iCnt, 7) = sel
        .Cells(iCnt, 8) = frmAddNewReport.txtAction
        .Cells(iCnt, 9) = frmAddNewReport.reqDate
        .Cells(iCnt, 10) = closeDate
        .Cells(iCnt, 11) = frmAddNewReport.closeEvid
        .Cells(iCnt, 12) = frmAddNewReport.closedBy
        .Cells(iCnt, 13) = CStr(frmAddNewReport.txtRTA)
        .Cells(iCnt, 14) = frmAddNewReport.cbxImpact
        .Cells(iCnt, 15) = frmAddNewReport.txtQAAction
        .Cells(iCnt, 16) = frmAddNewReport.txtSNBefore
        .Cells(iCnt, 17) = frmAddNewReport.txtSNAfter
        .Cells(iCnt, 18) = frmAddNewReport.txtNotes
    End With

This is the portion of my project that has the issue. Please let me know if you notice anything.