CODEWITHSUNDEEP
excelvba
GoldenVba
GoldenVba

Reputation: 11

Excel VBA: Loop Logic with ElseIf

I have a problem with a problem found on openclassroom. We have 3 numbers that we write in our sheet. Then we're supposed to categorize with 3 different functions which number is max, mid and min.

For more info about problem, see at the bottom of the problem. Here's my code for the problem:

Sub Problem3()
    Dim a, b, c As Double
    Dim maxnum, midnum, minnum As Double
    a = Range("A2").value
    b = Range("A3").value
    c = Range("A4").value


    Range("C2").value = Findmax(a, b, c)
    Range("C3").value = Findmid(a, b, c)
    Range("C4").value = Findmin(a, b, c)

End Sub
____

Function Findmax(num1, num2, num3) As Double
    If (num1 > num2 And num1 > num3) Then
        Findmax = num1
    ElseIf (num2 > num1 And num2 > num3) Then
        Findmax = num2
    ElseIf (num3 > num1 And num3 > num1) Then
        Findmax = num3
    End If
End Function
___

Function Findmid(x, y, z) As Double
    If (y < x < z Or z < x < y) Then
        Findmid = x
    ElseIf (y < z < x Or x < z < y) Then
        Findmid = z
    ElseIf (z < y < x Or x < y < z) Then
        Findmid = y
    End If
End Function
____

Function Findmin(num1, num2, num3) As Double
    If (num1 < num2 And num1 < num2) Then
        Findmin = num1
    ElseIf (num2 < num1 And num2 < num3) Then
        Findmin = num2
    ElseIf (num3 < num1 And num3 < num2) Then
        Findmin = num3
    End If
End Function

However, the answer I get for Findmid is always wrong (it's either equal to greatest number or to the lowest).

Here's the details of the problem:

Enter three numbers into cells A2, A3, and A4. Make sure that all three numbers are different. Create a run button on Sheet3 that executes a macro named problem3. This macro completes the following tasks:

• Obtain the three numbers from cells A2, A3, and A4 and store them in variables.

• Find the highest number using a Function procedure named FindMax. Here is an example of how to use that Function procedure. maxnum = FindMax(num1,num2,num3)

• Find the middle number using a Function procedure named FindMid.

• Find the lowest number using a Function procedure named FindMin.

• Output the highest number into cell C2, the middle number into cell C3, and the lowest number into cell C4.

For example, let’s say you input 27 into cell A2, 14 into cell A3, and 31 into cell A4.

When you click the run button, 31 will appear in cell C2, 27 will appear in cell C3, and 14 will appear in cell C4. This problem will require you to create a Sub procedure named problem3, and three Function procedures named FindMax, FindMid, and FindMin. You will be using FindMax, FindMid, and FindMin inside problem3. Write all procedures in one module. ** Do NOT use the built-in Min and Max functions from the Excel workbook environment to solve this problem. **

Use the Option Explicit statement for this macro.

Hint: Use one If-Then-ElseIf structure inside each Function procedure.

Upvotes: 1

Views: 72

Answers (2)

Tprograma
Tprograma

Reputation: 21

I think it is, the Max function, even I consider it is a application.Min function, as argument, that function get the cell range where that three numbers are. Otherwise use Switch case, maybe get the result, with three numbers it will be easy to debug.

Upvotes: -1

YowE3K
YowE3K

Reputation: 23974

Your logical statements, such as x < y < z are not doing what you think they are doing.

x < y < z is equivalent to (x < y) < z so, if x < y, that becomes True < z. As True is the same thing as -1, that then becomes -1 < z. And so on.

Rephrase each of these statements, so that

(y < x < z Or z < x < y)

becomes

((y < x) And (x < z)) Or ((z < x) And (x < y))

(They could probably be simplified a lot more, but that should get you started.)

Upvotes: 2

Related Questions