1

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
Vityata
  • 42,633
  • 8
  • 55
  • 100
Kat K
  • 13
  • 3
  • Works just fine with me. Maybe running through the code line by line (use `F8`) may help you to locate the problem? Also, try to avoid `Select` (see: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Perhaps the ranges you have in mind aren't actually inside `Sheets("expo")`? – ouroboros1 May 17 '22 at 13:56

1 Answers1

0

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
Vityata
  • 42,633
  • 8
  • 55
  • 100
  • Thank you for your help and advice! I think the Trim function was what I was missing! Everything works fine now :) – Kat K May 17 '22 at 14:36