Free Microsoft Excel 2013 Quick Reference

find and replace text in multiple .xls files

I think this can be easily done using Macro, but I cannot figure out how?

I have daily reports in multiple files for each day in Excel workbook. They are similar to each other. What I need to do, is to find a particular string and replace it with correct one. And this needs to be done with every report (58). Yes I can do it manually, but would appreciate if you can guide me on how to automate this process for future use.


Excel 2000 find and replace text in multiple autoshapes or comments, not in
cells

Hello, I need to find and replace text in a selection with text from another cell, however I need to do this a number of times without replacing previously replaced text.

I found this code by searching the forum and added to it, but it replaces previous changes. Is there any way I can lock the changes.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 30/10/2004 by dungee
'

'
Range("E5:G6").replace Range("e9").Text, Range("e10").Text
Range("E5:G6").replace Range("f9").Text, Range("f10").Text
Range("E5:G6").replace Range("g9").Text, Range("g10").Text
Range("E5:G6").replace Range("h9").Text, Range("h10").Text
Range("E5:G6").replace Range("i9").Text, Range("i10").Text

End Sub

Hope this makes sense.

Thanks, Chris

I need to find and replace text in certain cells an Excel sheet but with conditions:

For example:
If Cell in Column A="House" and Cell in Column B="Red" then rename Cell in Column B="Dark Red"

Do I do this with a macro? Please help. Thanks.

Hi all,

I was hoping someone could help me to write a code to replace text in multiple html or htm documents. I recently migrated a website to a new URL and need to replace all the static links. I realize this should not necessarily require excel, but excel is the only program I have any experience using VBA with, in fact the only VBA i currently have, so I figured I would start here.

I need to be able to pick a folder and have all the html or htm files within all the subfolders scanned for "123.com" and have it replaced with "ABC.net". There is no regular file structure inherent to these files so that really all I can give you. Is there a way to do this? If someone could at least point me in the right direction it would be great. Thanks!

Hi all - hope everyone is well. I have a situation that I'm hoping someone can help me resolve. My co-worker created a long Powerpoint presentation which contains about 90+ embedded charts/graphs. After sending the presentation to the client, the request was made to change the word "Caregiver" to "Influencers" in each and every chart. Obviously find and replace within Powerpoint doesn't pickup the word "caregiver" since it's in an embedded file. Of course I can go through and double-click each chart and make the change, but I was wondering if there's a way to write a macro in VBA to automate the task. I don't mind running the macro on each page if need be, but ideally, I'd love to run it once and have it change the word in all 90+ charts. I'm a complete novice with VBA, so I don't have a clue where to begin, so any help would be incredibly welcomed!

Thanks!
Tom

I am having trouble with this. I need to replace text in multiple hyperlinks. I found this code and it works great, except for the fact that it continues on to all worksheets in the workbook. I want it to replace only 1 worksheet at a time.

can anyone help with this? thanks in advance.

Sub ReplacePartHyperlinkAddress() 
    Dim hLink As  Hyperlink 
    Dim wSheet As  Worksheet 
     
    For Each wSheet In Worksheets 
        For Each hLink In wSheet.Hyperlinks 
            hLink.Address = Replace(hLink.Address, "old text", "new text") 
        Next hLink 
    Next wSheet 
End Sub


Hi,

what I am trying to do is to write some macro code to 'Find and Replace' certain text strings in each of my work sheet and replace with the correct text e.g. the macro will look in each worksheet in turn. find the text if it exists and replace with new text.

I have tried writing the code below but it appear only to work on the active worksheet

Dim mySheet As Worksheet

For Each mySheet In Worksheets
      
Cells.Replace What:="Test Me out", Replacement:="That Seems To work !!", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False


Cells.Replace What:="Test this as well", Replacement:="That Seems To work as well !!", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Next mySheet

End Sub

Any help would be greatly appreciated

Excel version 2003

Many thanks

Rob

I am looking for help. I have a large spreadsheet and need to replace "North Korea, South Korea" with "Korea." It shows up in column T, and it is in a long list of countries. When I try to do a find and replace, I get the error "Formula too long."

Thank you for any advice.

Hi guys,

First of all I'm not a programmer! I've been using the record macro facilty to create a macro to find and replace text. The code it generates is as follows:

Cells.Replace What:="ABCDEF", Replacement:="GHIJKL", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
False, ReplaceFormat:=False

I'm looking to run this code in column A only. However, it looks like if the search string exists in another column, the other column changes but not Column A.

How would I limit the code to a specific column or is there better coding for what I'm after?

Cheers
Roy

How can I change parts of VBA macros in multiple XLS files in a folder

- I Have a folder C: Temp it has over 100 identical files with identical layout of some tables.
- The names of these files are File1.xls, FileMain.xls, FileHome.xls ...etc
- Each file contains an identical Module1 in the VBA macro (Sub DeleteRow)
- Each file is locked for entry into the VBE and set the password "123"

I need a VBA macro that will in all files from folder to change the name of the macro in Module1

"Sub DeleteRow" change to "Private Sub deleteRow" (without the quotes)

Is there a way to modify the VBA macros in multiple files without launching any file?
Is it possible?

When using the find and replace function in Excel there is an option to specify whether to search within the sheet or the workbook. Does anyone know if it is possible to change the option from sheet to workbook or vice versa using VBA code?

How do I search and replace text in a textbox in Excel?

All,
I made a small mistake in one line of code of which there are a trillion
copies. Basically, I want to write code to find and replace lines in the
sheet modules.

ex. The sheet module has a line of code
Call JonMessedUp

I want to find all instances of that line in all the sheet modules and
change them to
Call JonDidNotMessUp

That's it.
Thanks in advance!!

--
J

Is it possible to turn off your find and replace function in Excel?

Hi,

In a workbook it is possible to find and replace data in cells and formulas.
Is it possible in any way to find & replace data in series in a chart object?

In have charts with more than 100 series on each chart and I would like to
be able to change all series on a chart using an automated routine.

Thanks

All,
I made a small mistake in one line of code of which there are a trillion
copies. Basically, I want to write code to find and replace lines in the
sheet modules.

ex. The sheet module has a line of code
Call JonMessedUp

I want to find all instances of that line in all the sheet modules and
change them to
Call JonDidNotMessUp

That's it.
Thanks in advance!!

--
J

Is it possible to turn off your find and replace function in Excel?

Hi,

In a workbook it is possible to find and replace data in cells and formulas.
Is it possible in any way to find & replace data in series in a chart object?

In have charts with more than 100 series on each chart and I would like to
be able to change all series on a chart using an automated routine.

Thanks

Hello Excel people,

I have a lot of excel files on which I need to run a 'find and replace'. I've found a number of apps on the web which do this if it's just the text on the sheet to change, but I can't find one which does FORMULAS.

I'm thinking I might have to write a VB script to do this, but it'd be great if anyone could help me out by (a) pointing me to some software - preferably free - which does this, or (b) posting some VB code which does it in a macro...

You'd save me a *lot* of time if you do - thanks in advance....

Hello all,

Need help in a VB macro - I have 17 worksheets in one excel file which has a lot of text. Each column has words such as Apple Icecream or Banana Sundae...etc.

In one of the worksheets i have listed acronyms such as Apple Icecream (cell A1) APPL IC (Cell b1), Banana Sundae (cell A2) BAN SD (cell b2)......

I have 100 such acronyms and want to change words across all sheets. Current i have the below noted code but there are 2 issues i face. 1) it doesnt do the typical find and replace. If the cell has Apple Icecream Large it would not replace it. 2) Its made to find and replace just one worksheet and not all in this workbook. Please help!!

Dim lookup As String
Dim replace As String

Sub find_replace()

Application.ScreenUpdating = False

r = LastRow()
Dim lr As Integer

For lr = r To 1 Step -1

Workbooks("Mapping_Macro.xls").Worksheets("Mapping").Activate

lookup = Cells(lr, 1).Value
replace = Cells(lr, 2).Value

Workbooks("Mapping_Macro.xls").Worksheets("abc").Select

Cells.replace _
What:=lookup, Replacement:=replace, _
LookAt:=xlWhole, MatchCase:=False

Next lr

End Sub

Function LastRow() As Long

Dim ix As Long
ix = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
LastRow = ix
End Function
[/FONT]

Good day all,

Please could you help me.
I need to open a .CSV file in notepad++ using a dialogue box and then find and replace text.
Please how do I go about all these.

Thanks

Greetings,

I have 500 .csv files that I need to edit as they are a backup of a database which at the end is being moved to a new device. In this process I need to:

- Take column A and format them as numbers(currently set as text) with 0 decimal places
- Take Column E,D, or F and any text in there needs to be cut and copied to the end of the text in column C

I was able to use notepadd++ for the other tasks which was a simple find and replace for all 500 workbooks but I do not think I can use it for the rest. In notepad++ I did a find for a 10 digit character which is on every other line and replaced it with the new number. I then did a search for any other text (,,,,) and had it deleted. The only thing it would not do was delete all the extra columns and rows which had no text.

Can you help?
Thanks!

I recently upgraded from Office 2000 to 2003. In Excel 2000 I was able to do
a find/replace to change the links in formulas for the entire page. Example:
Find
[file1.xls] Replace [file2.xls] and Replace all (in FileX). The confusing
thing is that when I try that in Excel 2003, it searches for the file for
each number changed, whereas when I had the two files open it didn't search
at all, and it gives me #REF errors for a lot of my numbers, even though the
two files are organized exactly the same(the only difference is the month's
data changes) Does anyone know why it has started doing this? Any help
would be much appreciated!!

I currently have a number of reports which pick up data from a large number of separate files. I periodically have to change the links e.g. if I have a new report for december I need to change to links from "data/Nov file" to "data/Dec file" - I have been doing this via find and replace but it is very slow. I have written a basic macro to try and speed this up but it does not help much. Can anyone suggest a macro or suggest a better way forward.