Looped If/Then Functions in Excel VBA

Question

I'm just starting to learn Excel VBA and I'm running into problems with a particular exercise. Given a column of 20 randomly generated integers between 0 and 100 in a column, I want to write a VBA program that writes in the column next to it "pass" if the number is greater than or equal to 50 and "fail" if the number is less than 50.

My approach involved using a looping function from i = 1 to 20 with an If statement for each cell (i,1) which would write pass or fail in (i,2).

Sub CommandButton1_Click()
'Declare Variables
Dim score As Integer, result As String, i As Integer

'Setup Loop function, If/Then function
For i = 1 To 20
    score = Sheet1.Cells(i, 1).Value
    If score >= 60 Then result = "pass"
    Sheet1.Cells(i, 2).Value = result
Next i

End If
End Sub

Could I get some insight into what I'm doing wrong?

Thanks in advance!

I'm not sure what the error is you're receiving, but it does look like you need to reset the value of result for each iteration of the loop. Maybe declare and initialize result inside the loop. — PerpetualStudent
– PerpetualStudent, Commented Jun 26, 2014 at 0:58

rivworks · Accepted Answer · 2014-06-26 01:13:30Z

1

Try something like this...

Sub CommandButton1_Click()
'Declare Variables
Dim score As Integer, result As String, i As Integer

'Setup Loop function, If/Then function
For i = 1 To 20
    score = Sheets("Sheet1").Cells(i, 1).Value
    If score >= 60 Then
        result = "pass"
    Else
        result = "fail"
   End If
    Sheets("Sheet1").Cells(i, 2).Value = result

Next i

End Sub

You need to properly specify the worksheet your working with like Sheets("Sheet1").Cells(...
Add an else clause to set result to fail when the value is less than 60. otherwise it never changes after the first 'pass'
Move the End if inside the for loop, immediately after the score check...

answered Jun 26, 2014 at 1:13

rivworks

261 bronze badge

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

Comments

Bernard Saucier · Accepted Answer · 2014-06-26 01:03:54Z

0

The correction with the least amount of changes is the following :

Sub CommandButton1_Click()
'Declare Variables
Dim score As Integer, result As String, i As Integer

'Setup Loop function, If/Then function
For i = 1 To 20
    score = Sheet1.Cells(i, 1).Value
    If score >= 60 Then 
        result = "pass"
        Sheet1.Cells(i, 2).Value = result
    End If
Next i

End If
End Sub

Keep in mind that, in VBA, variables are global to the function; not local to the loop. As it was mentionned, you could also have wrote something like :

result = ""
if score >= 60 then result = "pass"
sheet1....

answered Jun 26, 2014 at 1:03

Bernard Saucier

2,2601 gold badge21 silver badges29 bronze badges

Collectives™ on Stack Overflow

Looped If/Then Functions in Excel VBA

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related