Excel Programming VBA

Question

I have to display YES or NO from the given data of EXCEL . I have tried to write program . But i cant find the solution . It displays YES only not NO even if output should be NO . Please Help me with this program .

I have to check the data of whole column of C ( data is from number 2 to 1439 of the column). If the data is >=0.003 , It should display NO else it should display Yes.

CODE:

' Declare  array
Dim arrMarks(0 To 1437) As Double
 Sub ArrayLoops()

    ' Fill the array with numbers
    Dim i As Integer
    For i = LBound(arrMarks) To UBound(arrMarks)
        arrMarks(i) = Range("C2:C1439").Select
        Next i


    ' Using If statement

    For i = LBound(arrMarks) To UBound(arrMarks)
    If arrMarks(i) >= 0.003 Then
    MsgBox ("NO")
    Else
    End If

    Next i
    MsgBox ("YES")

End Sub

Isn't this something that would better left to an [excel-formula] or [conditional formatting]? — user4039065
– user4039065, Commented Nov 19, 2015 at 9:59

R3uK · Accepted Answer · 2015-11-19 11:48:16Z

1

Your "YES" was outside of both the loop and the test, so that is why you get it every time! ;)

Look at the way to fill an array directly from a range with .Value :

Sub ArrayLoops()
'Declare  array
Dim arrMarks()
Dim i As Integer
'Fill the array with range
arrMarks = Range("C2:C1439").Value

'Using If statement
For i = LBound(arrMarks, 1) To UBound(arrMarks, 1)
    If arrMarks(i, 1) >= 0.003 Then
        MsgBox ("NO")
    Else
        MsgBox ("YES")
    End If
Next i

End Sub

edited Nov 19, 2015 at 11:48

answered Nov 19, 2015 at 9:58

R3uK

14.6k8 gold badges49 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

aditya lamichhane Over a year ago

Thank you very much. But when i run the same code as yours it there was 'Run time Error 13': Type Mismatch. I dont know what it is and how to get rid of it . Can you please help me !

R3uK Over a year ago

When you have an error, plz also specify on which line! ;) My guess is that it is on the arrMarks = ... line, try the edit! ;)

R3uK Over a year ago

It should be highlighted in yellow! ;)

PaulG Over a year ago

If you have a type mismatch, it would suggest that the data has a different type in a cell. Depends on your error handling and whats calling the code when debugging. Try in the VBIDE selecting Tools:Options:General: Break On All Errors. That should stop exactly at the error. Don't forget to switch it back it its original setting though!

Patrick Lepelletier Over a year ago

to know wiwh line errors hit DEBUG when it errors, not END.

|

Collectives™ on Stack Overflow

Excel Programming VBA

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related