Loop for testing

[kinny2009]

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.

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

 

[mumps]

Assuming row 1 has headers and your data starts in row 2, try the following macro:

Sub kinny()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("J1:K" & LastRow).AutoFilter Field:=1, Criteria1:=">=200"
    Range("J1:K" & LastRow).AutoFilter Field:=2, Criteria1:="=Yes"
    Range("AV2:AV" & LastRow).SpecialCells(xlCellTypeVisible) = "passed"
    Range("J1").AutoFilter
    Application.ScreenUpdating = True
End Sub

 

[kinny2009]

Thanks, mumps

However, Error"1004" pops out

 

[rlv01]

Sub a()
    Dim i As Long

    With ActiveSheet
        For i = 0 To .UsedRange.Rows.count - 1
            If .Range("F1").Offset(i).Value >= 200 Then
                .Range("AV1").Offset(i).Value = "passed"
            End If
        Next i
    End With
End Sub

or

Sub a()
    Dim i As Long

    With ActiveSheet
        For i = 0 To .UsedRange.Rows.count - 1
            If .Range("F1").Offset(i).Value >= 200 And .Range("K1").Offset(i).Value = "Yes" Then
                .Range("AV1").Offset(i).Value = "passed"
            End If
        Next i
    End With
End Sub

 

[kinny2009]

Thanks, rlv01

But seems like all values in Col "AV" turn "passed" in regardless Col "F" & "K"...

 

[mumps]

Which line of code is highlighted when you click "Debug"?

 

[rlv01]

Thanks, rlv01

But seems like all values in Col "AV" turn "passed" in regardless Col "F" & "K"...

Then it sounds like you also have non-numeric data in col J, so add a test for numeric values:

Sub a()
    Dim i As Long

    With ActiveSheet
        For i = 0 To .UsedRange.Rows.Count - 1
            If IsNumeric(.Range("J1").Offset(i).Value) Then
                If .Range("J1").Offset(i).Value >= 200 And .Range("K1").Offset(i).Value = "Yes" Then
                    .Range(AV1").Offset(i).Value = "Passed"
                End If
            End If
        Next i
    End With
End Sub

 

[TotallyConfused]

Hello Kinny

I tried your code exactly the way you have it in posting #6 , and everything worked perfect. The only way I could get it not to put the word 'passed' in column 'AV', was to have the contents of column 'K' written as 'YES'. You will note this is in upper case letters. The way your 'IF' statement is written, VBA will search 'K' column for something written EXACTLY as you specified, which in your case is 'Yes'. The way to get around this problem is to change your code.

Instead of

[COLOR=black][FONT=Arial]If Cells(i, "J") >= 200 And Cells(i, "K") = "Yes" Then[/FONT][/COLOR]

change that line to

[COLOR=black][FONT=Arial]If Cells(i, "J") >= 200 And UCase(Cells(i, "K")) = "YES" Then[/FONT][/COLOR]

Doing this won't actually change anything written in your worksheet, but it changes the way VBA does the comparison. Just a side thought, but if you might have more than 32,767 rows of data, you will need to change the Dim statement to read

[COLOR=black][FONT=Arial]Dim RowsC as Long[/FONT][/COLOR]

I hope this helps.

TotallyConfused