Multiple ELSEIF statements-CodePudding

I am new to VBA and I need your help.

I have multiple ELSEIF statments in which I determine code of the doors depending on dimension.

I have more then 1000 different doors. And I dont want to write 1000 lines of code for each of them. many of them have the "same" statements and only one dimension is difference

How can I do this?

If ComboBox1 = "STANDARD" And Range("B11") = "ENOKRILNA" And Range("B10") = "POŽARNA VRATA PROGET EI60" And ComboBox2 = 800 And ComboBox3 = 2000 Then
Range("B13") = "PR0211.001"
ElseIf ComboBox1 = "STANDARD" And Range("B11") = "ENOKRILNA" And Range("B10") = "POŽARNA VRATA PROGET EI60" And ComboBox2 = 900 And ComboBox3 = 2000 Then
Range("B13") = "PR0211.002"
ElseIf ComboBox1 = "STANDARD" And Range("B11") = "ENOKRILNA" And Range("B10") = "POŽARNA VRATA PROGET EI60" And ComboBox2 = 1000 And ComboBox3 = 2000 Then
Range("B13") = "PR0211.003"
ElseIf ComboBox1 = "STANDARD" And Range("B11") = "ENOKRILNA" And Range("B10") = "POŽARNA VRATA PROGET EI60" And ComboBox2 = 1300 And ComboBox3 = 2000 Then
Range("B13") = "PR0211.004"
ElseIf ComboBox1 = "STANDARD" And Range("B11") = "ENOKRILNA" And Range("B10") = "POŽARNA VRATA PROGET EI60" And ComboBox2 = 800 And ComboBox3 = 2050 Then
Range("B13") = "PR0211.011"
ElseIf ComboBox1 = "STANDARD" And Range("B11") = "ENOKRILNA" And Range("B10") = "POŽARNA VRATA PROGET EI60" And ComboBox2 = 900 And ComboBox3 = 2050 Then
Range("B13") = "PR0211.012"
ElseIf ComboBox1 = "STANDARD" And Range("B11") = "ENOKRILNA" And Range("B10") = "POŽARNA VRATA PROGET EI60" And ComboBox2 = 1000 And ComboBox3 = 2050 Then
Range("B13") = "PR0211.013"

CodePudding user response：

You can try to use "while" or "for" loops.

I made a quick example. However, you will need to modify it for your use case:

Dim x, y, i As Double

x = 900
y = 2000
i = 1


If ComboBox1 = "STANDARD" And Range("B11") = "ENOKRILNA" And Range("B10") = "POŽARNA VRATA PROGET EI60" Then

    While x < 50000 And y < 60000
    
        Range("B13") = "PR0211." & CStr(i)
        
        i = i   1
        x = x   100
        y = y   100
    
    Wend

End If

CodePudding user response：

It may be possible for you to build a dictionary to greatly simplify your code.

You don't provide enough to for us to know how you derive the values for combo2 and combo3 (very bad names by the way, doesn't give any information on what the values are used for).

You also have a lot of redundancy in your testing which is not necessary for the specific case in the code you have provided.

Somewhere during intialisation you would create your dictionary that represents the keys as the combinations of Combo2 and Combo3 that are possible, with the item being the value you wish to write to Cell B13. In this case we will use a Scripting.Dictionary because of the facility to get arrays of the Keys and Items should you need them elsewhere in your code.

So during initialisation you would have something like the code below

Dim B13Result as Scripting.DIctionary
Set B13Result = New Scripting.Dictionary
' Ideally you'd want to set up the dictionary programatically
With B13Result
    .Add "800#2000", "PR0211.001"
    .Add "900#2000", "PR0211.002"
    .Add "1000#2000", "PR0211.003"
'etc etc
End With

You would then replace your code above with something like the following

Public Sub AssignB13

    If ComboBox1 <> "STANDARD" Then exit function
    If Range("B11") <> "ENOKRILNA" then exit function
    If Range("B10") <>"POŽARNA VRATA PROGET EI60"  then exit function

    Dim myKey as string
    myKey = cstr(ComboBox2) & "#" & cstr( ComboBox3)
    Range("B13") = B13Result.Item(myKey)

End Sub

The use of the dictionary makes life a whole lot easier assuming you data allows for such use.

CodePudding user response：

You could do this with a helper worksheet and INDEX and MATCH. You don't give enough data to show a complete example, but please see how this could easily be accomplished below:

The formula used is:

=INDEX(F2:F5,MATCH(1,INDEX((B8=A2:A5)*(B9=B2:B5)*(B10=C2:C5)*(B11=D2:D5)*(B12=E2:E5),0,1),0))

You can find an example here: Multi-value lookup