I am trying to create a macro button that will help me update the the value in the AE column to "N" if the value in the same row of the H column is "REPO".

I am not sure why my code doesn't work properly and just seems to select the AE column when I run it instead of changing the values to "N"

Sub Change_Repo_Risk_to_N()     

    Sheets("expo").Select    
    Dim LastRow As Long    
    Dim i As Long    
    LastRow = Range("H" & Rows.Count).End(xlUp).Row

    For i = 2 To LastRow           
        If Range("H" & i).Value = "REPO" Then    
            Range("AE" & i).Value = "N"    
        End If   
    Next i      

End Sub

CodePudding user response：

Probably mistake due to one if these 3:

• Lack of Trim()
• Lack of UCase() (Option Compare Text is an alternative of this one)
• Select() is too slow and does not refer correctly to the worksheet (try to avoid it)

Try this one:

Sub ChangeRepoRiskToN()
 
    With Worksheets("expo")
        Dim lastRow As Long
        Dim i As Long
    
        lastRow = .Range("H" & Rows.Count).End(xlUp).Row
            For i = 2 To lastRow
                If Trim(UCase(.Range("H" & i).Value)) = "REPO" Then
                    .Range("AE" & i).Value = "N"
                End If
            Next i
    End With

End Sub