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
()
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

soadfan

New Member
Joined
Jul 30, 2015
Messages
46
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
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,337
Office Version
  1. 2010
Platform
  1. Windows
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:

soadfan

New Member
Joined
Jul 30, 2015
Messages
46
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:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,337
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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:

kinny2009

New Member
Joined
Jul 7, 2016
Messages
18
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:

soadfan

New Member
Joined
Jul 30, 2015
Messages
46

ADVERTISEMENT

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:

kinny2009

New Member
Joined
Jul 7, 2016
Messages
18
Hi Soadfun, kind of hard to understand.

Would you please help to modify the code.

Cheers, Bro!
 

soadfan

New Member
Joined
Jul 30, 2015
Messages
46
You should count leading column. In this case, it's probably "J" so the code you wrote should do the trick
 

Watch MrExcel Video

Forum statistics

Threads
1,109,538
Messages
5,529,429
Members
409,876
Latest member
Akash Yadav
Top