CODEWITHSUNDEEP
arraysvbaexcel
Michael Tiffany
Michael Tiffany

Reputation: 13

Why my If condition is returning wrong result?

I'm a student currently studying VBA in one of my classes, where the current assignment is to pull data from a .txt file and display it, as well as total it, and then grade the total. using arrays I've been successful in the first two parts using arrays, but when trying to factor in the total for a grade the array only takes the starting numbers into account. Any thoughts? Code below

Sub Categories()

Dim Locale As String, State(1 To 50) As Variant
Dim Serial(1 To 50) As Single, i As Single
Dim path As String, j As Single
Dim Score(1 To 50, 1 To 7) As Single
Dim IndexGrade(1 To 50) As Single
Dim Total(1 To 50) As Single
Locale = ActiveWorkbook.path

path = Locale & "US_States.txt"

Open path For Input As #1

For i = 1 To 50 Step 1
    Input #1, Serial(i), State(i)
    Sheet1.Cells(1 + i, 1).Value = Serial(i)
    Sheet1.Cells(1 + i, 2).Value = State(i)
         For j = 1 To 7 Step 1
            Input #1, Score(i, j)
            Total(i) = Total(i) + Score(i, j)
            Sheet1.Cells(1 + i, 3).Value = Total(i)
        Next j
   Total(i) = Sheet1.Cells(1 + i, 3).Value
   If 0 <= Total(i) < 100 Then
    Sheet1.Cells(1 + i, 4).Value = "A"
    ElseIf 100 <= Total(i) < 200 Then
    Sheet1.Cells(1 + i, 4).Value = "B"
    ElseIf 200 <= Total(i) < 300 Then
    Sheet1.Cells(1 + i, 4).Value = "C"
    ElseIf 300 <= Total(i) Then
    Sheet1.Cells(1 + i, 4).Value = "D"
    End If
Next i

Close #1

End Sub

Upvotes: 1

Views: 40

Answers (1)

cyboashu
cyboashu

Reputation: 10433

Problem is with your If condition. In VBA 1 < 2 < 1 evaluates to true. That's why even if your total(i) is more than 100, it always evaluates to true and your elseif is not coming into play.

In VBA/VB6, type conversion is simply evil.

You nee to rewrite your If and elseif conditions

Example:

Sub test()

    Dim x   As Long
    Dim y   As Long

    x = 101
    y = 99

    '/ What you are doing
    If 0 <= x < 1 Then
        MsgBox "This is not python."
    End If

    '/ How you should do it.
    If y >= 0 And y < 100 Then
        MsgBox "This is how you do it in VBA."
    End If

End Sub

Upvotes: 2

Related Questions