Solved

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

Posted on 2016-09-26
3
76 Views
Last Modified: 2016-09-26
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
Comment
Question by:Omar Hernandez
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 31

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 500 total points
ID: 41815545
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
 

Author Closing Comment

by:Omar Hernandez
ID: 41815886
Works great, thanks again Neeraj really appreciated.
0
 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41815899
You're welcome Omar! Glad to help.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question