Find and Replace on Workbook with Lists

[LadyAmpherion]

I am new to the world of macros, other than I know that they exist. I know that if I can do it in Excel, then I can also do it with a Macro. Well I finally have a need.

I know that I can do the following:

Select all tabs in a workbook
Find TEXT and replace with TEXT where TEXT is now in a GREEN cell
Repeat with 134 times with each "text"

What I would like to do is use my "data validation" list in a Macro and combine that with a Find and Replace Macro, to do this for me.

Has anyone already done this perhaps? Or has a really elegant way of combining these two thoughts? I would appreciate the assistance.

Thanks in advance!

 

[dmt32]

I am new to the world of macros, other than I know that they exist. I know that if I can do it in Excel, then I can also do it with a Macro. Well I finally have a need.

I know that I can do the following:

Select all tabs in a workbook
Find TEXT and replace with TEXT where TEXT is now in a GREEN cell
Repeat with 134 times with each "text"

What I would like to do is use my "data validation" list in a Macro and combine that with a Find and Replace Macro, to do this for me.

Has anyone already done this perhaps? Or has a really elegant way of combining these two thoughts? I would appreciate the assistance.

Thanks in advance!

Hi not a solution to your post but to give an idea one way you could approach your requirement:

Place this code in worksheets code page where you have your data validation:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
        Application.EnableEvents = False
        FindReplace Me, Target.Value
        Application.EnableEvents = True
    End If
End Sub

in this example, I have assumed your data validation is in the range A1:A10 & that the search string value comes from the selected cell.

Place this code in a standard module:

Sub FindReplace(ByVal ws As Worksheet, ByVal Search As String)
    Dim sReplace As String
    Dim Prompt As String
    Dim Title As String

    Prompt = "Search For: " & Search & Chr(10) & Chr(10) & _
    "What is the Replace value?"
    Title = "Search Value Input"
    sReplace = InputBox(Prompt, Title)
    If Len(sReplace) = 0 Then Exit Sub
    ws.Range("C10:F20").Replace What:=Search, Replacement:=sReplace, _
                     LookAt:=xlPart, MatchCase:=False
End Sub

Again in the example, C10:F10 of the search worksheet is the search / replace range.

You can pass any worksheet in your workbook to the procedure I have just used the activesheet with data validation but adapt as required.

Hope gives you some ideas.

Dave

 

[LadyAmpherion]

So here are the steps that I would like to accomplish.

1. Search Workbook (should be able to skip select all tabs step)
2. Find all values = ***A***
3. Replace all values with same value = ***A*** but with color format

***A*** Using a Listing of Pre-Defined Values about 200 different values approximately


So I started with recording a Macro to do this with the formatting and selecting the entire workbook. It works within that workbook that is open, but future workbooks only work in the active tab.

I tried changing the LookAt:=xlSheet to LookAt:=xlWhole and I noticed the example above using xlPart. *** sigh ***

now it wont run at all ...

My hope was that if I could get it to work this way, then I could use the define list that I have seen referenced here and there.

So while googling around, I found one that searches the whole workbook but stops at the first value found
I found another that searches the whole workbook, and will continue to search the whole workbook, but only finds the first value on each tab.

I need to be able to find EVERY VALUE on EVERY TAB.

anyone? have any great ideas

 

[LadyAmpherion]

Hi Dave,

So as you can probably tell I'm a total Novice when it comes to all of this. I cannot seem to put the 2nd set of code into my worksheet successfully. It merges with the private code and then there is nothing to run. Is the design such that my Data Validation tab is in a completely seperate workbook? If so, that works too ... as I have to check several workbooks for this same data.

Thanks again!