Excel VBA Multiple if statement in loop working incorrectly-CodePudding

Sorry in advance if this has been covered already, I did try to look around first.

I have multiple if statements inside a loop and the conditions of the if statements are not being adhered to. I am working on fluid flow through pipe and the conditions for my calculation change with which range of numbers the reynolds number is between.

no-slip Reynolds < 2000 is laminar no-slip Reynolds > 4000 is turbulent between is transition

I have all three conditions in their own columns and the if statements should choose the correct one based on the Reynolds number and goes down all of the rows in the sheet.

        For i = 6 To rows   6 Step 1
            
            If Range("BQ" & i).Value <=2000 Then
                Range("BV" & i).Value = Range("BR" & i).Value
            End If
            If Range("BQ" & i).Value >= 4000 Then
                Range("BV" & i).Value = Range("BT" & i).Value
            End If
            If 2000 < Range("BQ" & i).Value < 4000 Then
                Range("BV" & i).Value = Range("BU" & i).Value
            End If
        Next i

RESULT:

Excel sheet

As you can see the Fns column is being filled by Fns Transition even though the reynolds number is under 2000.

CodePudding user response：

Bringing this down from my comment:

2000 < Range("BQ" & i).Value AND Range("BQ" & i).Value < 4000 instead. Otherwise you end up with one half of that inequality range being solved for (True < 4000) and then the resulting boolean being compared with 4000, which will always be True (I think). Also, instead of separate if you should be using elseif in here since only one of these conditions should be true at a time.

    For i = 6 To rows   6 Step 1            
        If Range("BQ" & i).Value <=2000 Then
            Range("BV" & i).Value = Range("BR" & i).Value
        ElseIf Range("BQ" & i).Value >= 4000 Then
            Range("BV" & i).Value = Range("BT" & i).Value
        ElseIf 2000 < Range("BQ" & i).Value AND Range("BQ" & i).Value < 4000 Then
            Range("BV" & i).Value = Range("BU" & i).Value
        End If
    Next i

CodePudding user response：

You might have more luck with a Select Case statement:

Select Case Range("BQ" & i).Value
    Case Is <= 2000
        Range("BV" & i).Value = Range("BR" & i).Value
    Case Is >= 4000
        Range("BV" & i).Value = Range("BT" & i).Value
    Case Else
        Range("BV" & i).Value = Range("BU" & i).Value
End Select

This is easier to read and debug later as well. Or an Excel formula could do this:

=SWITCH(true,BQ2<=2000,BV2,BQ2>=4000,BT2,BU2)