Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 86
  • Last Modified:

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

0
Omar Hernandez
Asked:
Omar Hernandez
  • 2
1 Solution
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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

0
 
Omar HernandezAuthor Commented:
Works great, thanks again Neeraj really appreciated.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Omar! Glad to help.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now