Using VBA to find and replace text.

[Dangerous Damian]

I could use a helping hand on this one. I really need to learn VBA. Here's the scenario: I have an Excel template (I save it using 97-03 compatibility mode. Everyone else in the office is on 03') used for the creation of a client's introduction packet to a service program we offer. There are five worksheets contained in the template. I am trying to get Excel to auto-run a macro when the content of a certain cell changes. That cell is located in "book1" of the Workbook. The contents of the cell is set as a list with a drop down menu. This cell contains the company name. (15 different companies). When this cell is modified I need Excel to find and replace a specified text in the last tab (book5). Book5 contains roughly 18 pages. The names to be changed are that of the various companies we service. Also, the pages are in paragraph or letter form so there are a few variations on how the names should appear. Here are the various forms:
1. "company"
2. "company's"
3. "company."
4. "company,"

Could someone with advanced VBA knowledge help me out with the code? It would be much appreciated. Thank you. This is my first post. Let me know if I am not providing enough details. Thanks again.

 

[mole999]

not sure you really need a vba module, if each of the cells you need to change read as =sheet,cell then as you change one they should all change, and that wouldn't be limited to 15, it could be any number you like. to demonstrate on a blank sheet type a word, in another cell type =(and the cell reference i.e B3) i.e =(B3) and see the result.

as for the changes you could incorporate =(b3)&"." to add the full stop or =(b3)&"'s" etc

 

[Dangerous Damian]

Thanks for the suggestion. However, the problem is that I have all the pages on "book5" as merged single cells. I did this to match the formatting of the original documents (it was a Word file). Any suggestions, with that detail in mind?

 

[mole999]

I'm thinking with a worskheet change event and using target / intersect to trigger the find replace dialogue

    Sheets("Sheet5").Select
    Cells.Replace What:="Wednesday", Replacement:="Friday", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

if the original default value is known then Replacement code could be pointed to a cell that has your new value triggered for the event

how does that sound

 

[Dangerous Damian]

I'll try that. Thanks, again. I want to learn VBA, any suggestions on a good reference book or resource for beginners? Much appreciated.

 

[mole999]

This almost works, just can't get the find replace strings to take

Private Sub Worksheet_Change(ByVal Target As Range)
'   Code goes in the Worksheet specific module
    Dim rng As Range, subvalue As String
    subvalue = " " & Range("a1") & " "
    '   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")
    Set rng = Target.Parent.Range("A1")
    '   Only look at single cell changes
    If Target.Count > 1 Then Exit Sub
    '   Only look at that range
    If Intersect(Target, rng) Is Nothing Then Exit Sub
    '   Action if Condition(s) are met
    '   Do your thing here

    Sheets("book5").Select
    Cells.Replace What:="DefaultValue", Replacement:="subvalue", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
End Sub

 

[mole999]

and this works quite well for my small test

="once upon a " & A1 & ", I got that " & A1 & " Feeling"

 

[kylefoley76]

I've been working on this bug for about 5 years, maybe this is the year that I'll finally get rid of it. In any case, when I use this code:


Dim gg As Range
Set gg = Range("a1:s16000")
gg.Replace What:="::1", Replacement:=ChrW(&H2081), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

Sometimes the subscript will be in font OpenProofBold, sometimes it will be in Noteworthy Bold. The Noteworthy Bold is utterly awful, ugly and horrible to look at it. When I open and close the workbook sometimes the font for the subscript is OpenProofBold, sometimes it's Noteworthy and utterly at random. There is no rhyme or reason to it. I'm trying to figure out how to alter the font of just that one character to openproofbold. I cannot cntrl A and make everything openproof bold because that would make things very hard to read.

 

[kylefoley76]

I'll just go through each cell and if there is a superscript in them then I'll make alterations on this code:

Range("a2").Characters(1, 2).Font.Name = "Cambria"

That should do it.