CODEWITHSUNDEEP
vbaexcel
Lilou
Lilou

Reputation: 33

Comparing two cells using Select Case or If Then VBA

I am trying to get different results in cell "C8" depending on the relationship between the values of the cells "B3" and "C3"

I first tried with a basic Select Case :

    Sub Salmonpool_depth1()
Dim score As Variant, result As String
With Sheets("Vertical")
    score = Range("C3").Value
Select Case score
    Case Is = ""
        result = ""
    Case Is >= 0.3 * Range("B3").Value
        result = "0.3"
    Case Is >= 0.6 * Range("B3").Value
        result = "0.6"
    Case Is >= Range("B3").Value
        result = "1"
    Case Else
        result = "0"
End Select
Range("C8").Value = result
End With
End Sub

But it always gave me the result 0.3 as a result if any conditions were filled other than the last one, it did give me 0 when the last condition was filled.

I then tried by defining B3 and C3 as variables

Sub Salmonpool_depth2()
Dim pool As Variant, result As String, hydraulic As Variant
With Sheets("Vertical")
    pool = Range("C3").Value
    hydraulic = Range("B3").Value
Select Case pool
    Case Is = ""
        result = ""
    Case Is >= 0.3 * hydraulic
        result = "0.3"
    Case Is >= 0.6 * hydraulic
        result = "0.6"
    Case Is >= hydraulic
        result = "1"
    Case Else
        result = "0"
End Select
Range("C8").Value = result
End With
End Sub

But that also gave me 0.3 or 0 as a result as above

I then tried with If Then statements instead :

Sub Salmonpool_depth3()
Dim hydraulic As Variant, pool As Variant
hydraulic = Range("B3").Value
pool = Range("C3").Value

If pool >= hydraulic Then
Range("C8").Value = 1
End If
If pool >= 0.6 * hydraulic Then
Range("C8").Value = 0.6
End If
If pool >= 0.3 * hydraulic Then
Range("C8").Value = 0.3
End If
If pool < 0.3 * hydraulic Then
Range("C8").Value = 0
End If
If pool = "" Then
Range("C8").Value = ""
End If
End Sub

But that also gives me 0.3 or 0 as above.

Does anyone have any idea how to change this? It must be in the way that I ask the question as the program does not understand.

Lilou

Upvotes: 0

Views: 1830

Answers (4)

gualdhar
gualdhar

Reputation: 3

I know that Tim, Doug and JC have all answered your question for cases, but as you said your if statements were also giving improper results, and I'm not sure anyone addressed that. As written, changing the order would fix the result, but the more elegant solution is using the Else If statement. That way, you get a situation similar to using Case, where it checks each statement until one is right, does the section of code in that section, and skips ahead to End If when its done. It would look something like this:

If pool >= hydraulic Then
    Range("C8").Value = 1
Else If pool >= 0.6 * hydraulic Then
    Range("C8").Value = 0.6
Else If pool >= 0.3 * hydraulic Then
    Range("C8").Value = 0.3
Else If pool < 0.3 * hydraulic Then
    Range("C8").Value = 0
Else If pool = "" Then
    Range("C8").Value = ""
End If

This way, lets say pool was 0.7*hydraulic, it would throw False for the first If statement, then True for the next Else If line, set C8 to 0.6, and move to the End If line. Your way (putting End If after each If statement) checks each If statement sequentially, whether something passes the previous statement or not. You can also make this more robust by requiring multiple conditionals to pass each if:

If pool >= hydraulic Then
    Range("C8").Value = 1
Else If pool >= 0.6 * hydraulic And pool < hydraulic Then
    Range("C8").Value = 0.6
Else If pool >= 0.3 * hydraulic And pool < 0.6 * hydraulic Then
    Range("C8").Value = 0.3
Else If pool < 0.3 * hydraulic Then
    Range("C8").Value = 0
Else If pool = "" Then
    Range("C8").Value = ""
End If

With this snipet, you can reorder the conditionals however you like, and it will only pass if it falls within that specific range. And, if you like, you can place an Else line by itself at the end of the section that will be run if none of the If or Else If statements pass:

If pool >= hydraulic Then
    Range("C8").Value = 1
Else If pool >= 0.6 * hydraulic And pool < hydraulic Then
    Range("C8").Value = 0.6
Else If pool >= 0.3 * hydraulic And pool < 0.6 * hydraulic Then
    Range("C8").Value = 0.3
Else If pool < 0.3 * hydraulic Then
    Range("C8").Value = 0
Else If pool = "" Then
    Range("C8").Value = ""
Else
    Range("C8").Value = "Invalid Entry"
End If

Upvotes: 0

Doug Coats
Doug Coats

Reputation: 7107

the problem is that if its greater than .03 then it will always stop there. SELECT CASE Statements exit when one of the conditions evaluates to try. You would need to order it in such a fashion:

    Case Is >= hydraulic
         result = "1"
    Case Is >= 0.6 * hydraulic
         result = "0.6"
    Case Is >= 0.3 * Range("B3").Value
         result = "0.3"

Upvotes: 1

jcarroll
jcarroll

Reputation: 577

Using the first bit of code you posted, I think your >= is backwards. It should be <=.

Sub Salmonpool_depth1()

Dim score As Variant, result As String

With Sheets("Vertical")

    score = Range("C3").Value

    Select Case score
        Case Is = ""
            result = ""
        Case Is <= (0.3 * Range("B3").Value)
            result = "0.3"
        Case Is >= (0.6 * Range("B3").Value)
            result = "0.6"
        Case Is >= Range("B3").Value
            result = "1"
        Case Else
            result = "0"
    End Select

Range("C8").Value = result

End With

End Sub

Upvotes: 0

Tim
Tim

Reputation: 2892

Your problem in the Select Case statements is that once a condition is met, it quits comparing. So when your condition is...say 0.8, the following code says "Is it greater than or equal to 0.3 * Hydraulic? Yes" and then never compares the 0.6 * Hydraulic. 

Case Is >= 0.3 * hydraulic
    result = "0.3"
Case Is >= 0.6 * hydraulic

You need to put a limit on the first comparison like this:

Case (0.3 * hydraulic) To (0.6 * hydraulic)
    result = "0.3"
Case (0.6 * hydraulic) To 1

Upvotes: 1

Related Questions