Loop for testing

kinny2009

New Member
Joined
Jul 7, 2016
Messages
18
Hi guys,

Just writing a script for testing "J" columns value.

If it's >= 200, then "AV" column show "passed"

But seems like below doesn't work.

Thanks for your help in advance.

Code:
Sub a()

Dim i As Long

For i = 1 To Rows.Count

If Cells(i, "J").Value >= 200 Then

Cells(i, "AV").Value = "passed"

End If

Next i

End Sub
()
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
seems about right, except rows.count

Code:
Sub test()
Dim RowsC As Integer
RowsC = Cells(Rows.Count, "F").End(xlUp).Row

For i = 117 To RowsC
If Cells(i, "F") >= 70 Then
Cells(i, "i").Value = "passed"
End If
Next i

End Sub
 
Upvote 0
seems about right, except rows.count

Code:
Sub test()
Dim RowsC As Integer
RowsC = Cells(Rows.Count, "[B][COLOR="#FF0000"]F[/COLOR][/B]").End(xlUp).Row

For i = [B][COLOR="#FF0000"]117[/COLOR][/B] To RowsC
If Cells(i, "[B][COLOR="#FF0000"]F[/COLOR][/B]") >= [B][COLOR="#FF0000"]70[/COLOR][/B] Then
Cells(i, "[B][COLOR="#FF0000"]i[/COLOR][/B]").Value = "passed"
End If
Next i

End Sub
:confused:
 
Last edited:
Upvote 0
Well i did test in my own file.
I thought he will use his code and will change just counting part, at least this is what i would do
 
Last edited:
Upvote 0
Well i did test in my own file.
I thought he will use his code and will change just counting part, at least this is what i would do
Probably better to use the references and constants the OP posts in case his/her (or some future reader's) skill level is such that modifying your code might be problematic for him/her.
 
Last edited:
Upvote 0
Thanks guys, it works

Another follow up-question.

Should i set another rowscount if the "if" function extend to multiple criteria with column "K"

Let's say if "J" column >= 200, and "K" column is "Yes"

Then "AV" column = "passed"

Code:
Sub test()
Dim RowsC As Integer
RowsC = Cells(Rows.Count, "J").End(xlUp).Row


For i = 1 To RowsC
If Cells(i, "J") >= 200 And Cells(i, "K") = "Yes" Then
Cells(i, "AV").Value = "passed"
End If
Next i


End Sub
 
Last edited:
Upvote 0
Pick the highest priority column for counting. I mean determine which is the leading column and count only cells in it. So the one count variable is fine
 
Last edited:
Upvote 0
You should count leading column. In this case, it's probably "J" so the code you wrote should do the trick
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top