Untitled

mail@pastecode.io avatar
unknown
plain_text
21 days ago
1.2 kB
4
Indexable
Never
Sub ReplaceReasonsInColumnL()
    Dim ws As Worksheet
    Dim stationNumber As Integer
    Dim reason As String
    Dim cell As Range
    Dim rng As Range

    ' Define the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change Sheet1 to your sheet name

    ' Define the range of cells in column L to check (filtered visible cells only)
    On Error Resume Next ' Ignore errors in case there are no visible cells
    Set rng = ws.Range("L2:L" & ws.Cells(ws.Rows.Count, "L").End(xlUp).Row).SpecialCells(xlCellTypeVisible)
    On Error GoTo 0 ' Turn error handling back on

    ' Check if there are any visible cells
    If Not rng Is Nothing Then
        ' Loop through each station number
        For stationNumber = 201 To 216
            ' Define the standardized reason
            reason = "ST" & stationNumber & " [your reason]" ' Customize the reason text if needed

            ' Loop through each cell in the defined range
            For Each cell In rng
                If InStr(cell.Value, CStr(stationNumber)) > 0 Then
                    ' Replace the cell value with the standardized reason
                    cell.Value = reason
                End If
            Next cell
        Next stationNumber
    End If
End Sub
Leave a Comment