Looping through a row-CodePudding

I'm trying to write a VBA code whereby, for each row in a given range, if the string "Y" appears at all in a given row, then column "V" of the same row, will be populated with "Y". If no "Y" is found at all, i.e every value in the row is "N", then column "V" will be populated with "N".

Below is my code :

Public Sub test()
Dim lastrow as Long, i as Long, x As Long
lastrow = Cells(Rows.Count, "U").End(xlUp).Row

For i = 2 to lastrow
   For x = 23 to 51
      If Cells(i,x).Value = "N" Then
        Range("V" & i).value = "N"
        Else
        Range("V" & i).Value = "Y"
      End If
   Next x
Next i
End Sub

For some reason, when I run this code, column "V" is populated with "N" for every row, despite there being some "Y"s in the given range.

Thanks!

CodePudding user response：

You can use CountIf() to check the row:

Public Sub test()
    Dim i as Long, numYes As Long, ws As WorkSheet
    
    Set ws = ActiveSheet 'or whatever
    For i = 2 to ws.Cells(Rows.Count, "U").End(xlUp).Row
        numYes = Application.CountIf(ws.Cells(i, "W").Resize(1, 29), "Y")
        ws.Cells(i, "V").Value = IIf(numYes > 0, "Y", "N")    
    Next i
End Sub

CodePudding user response：

Check Matches in Row Ranges (Loop)

Option Explicit

Sub FlagData()
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    Dim LastRow As Long: LastRow = ws.Cells(ws.Rows.Count, "U").End(xlUp).Row
    
    Dim ccrg As Range: Set ccrg = ws.Columns("W:AY") ' Check Columns Range
    Dim fcrg As Range: Set fcrg = ws.Columns("V") ' Flag Column Range
    
    Dim cCell As Range
    Dim r As Long
    Dim YesFound As Boolean
    
    For r = 2 To LastRow
        
        For Each cCell In ccrg.Rows(r).Cells
            If StrComp(CStr(cCell.Value), "Y", vbTextCompare) = 0 Then
                YesFound = True
                Exit For
            End If
        Next cCell
        
        If YesFound Then
            fcrg.Rows(r).Value = "Y"
            YesFound = False
        Else
            fcrg.Rows(r).Value = "N"
        End If
    
    Next r

    MsgBox "Data flagged.", vbInformation

End Sub