We help IT Professionals succeed at work.

Column "F" if word found is (ZCOMPLETED) have it appear on column "C" (Z) and (HOLD) appear (H)

on this module i would like to create an additional request that when it finds the word (ZCOMPLETED) have it appear on column "C" (Z)
and (HOLD) Appear (H). thanks for your time and help guys.

Sub FindAndPlaceValuesInColumnC()
Dim lr As Long
Dim rng As Range, cell As Range
Application.ScreenUpdating = False
Application.EnableEvents = False
lr = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
ActiveSheet.ListObjects("Table134").Range.AutoFilter field:=7, Criteria1:="1"
If Range("G4:G" & lr).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
   Set rng = Range("F5:F" & lr).SpecialCells(xlCellTypeVisible)
   For Each cell In rng
      If InStr(Cells(cell.Row, "C").Value, " (") = 0 Then
         Cells(cell.Row, "C") = Cells(cell.Row, "C") & " (" & Trim(Replace(cell.Value, "*", "")) & ")"
      Else
         Cells(cell.Row, "C") = WorksheetFunction.Replace(Cells(cell.Row, "C").Value, InStr(Cells(cell.Row, "C"), " ("), 255, " (" & Trim(Replace(cell.Value, "*", "")) & ")")
      End If
   Next cell
End If
ActiveSheet.ListObjects("Table134").Range.AutoFilter field:=7
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Open in new window

Comment
Watch Question

Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
Please try this....
Sub FindAndPlaceValuesInColumnC()
Dim lr As Long
Dim rng As Range, cell As Range
Application.ScreenUpdating = False
Application.EnableEvents = False
lr = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
ActiveSheet.ListObjects("Table134").Range.AutoFilter field:=7, Criteria1:="1"
If Range("G4:G" & lr).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
   Set rng = Range("F5:F" & lr).SpecialCells(xlCellTypeVisible)
   For Each cell In rng
      If IsNumeric(Trim(Replace(cell.Value, "*", ""))) Then
         If InStr(Cells(cell.Row, "C").Value, " (") = 0 Then
            Cells(cell.Row, "C") = Cells(cell.Row, "C") & " (" & Trim(Replace(cell.Value, "*", "")) & ")"
         Else
            Cells(cell.Row, "C") = WorksheetFunction.Replace(Cells(cell.Row, "C").Value, InStr(Cells(cell.Row, "C"), " ("), 255, " (" & Trim(Replace(cell.Value, "*", "")) & ")")
         End If
      ElseIf cell.Value Like "ZCOMPLETED*" Or cell.Value Like "HOLD*" Then
         If InStr(Cells(cell.Row, "C").Value, " (") = 0 Then
            Cells(cell.Row, "C") = Cells(cell.Row, "C") & " (" & Left(cell.Value, 1) & ")"
         Else
            Cells(cell.Row, "C") = WorksheetFunction.Replace(Cells(cell.Row, "C").Value, InStr(Cells(cell.Row, "C"), " ("), 255, " (" & Left(cell.Value, 1) & ")")
         End If
      End If
   Next cell
End If
ActiveSheet.ListObjects("Table134").Range.AutoFilter field:=7
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Open in new window

Author

Commented:
Works great, thanks again Neeraj really appreciated.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome Omar! Glad to help.