Free Microsoft Excel 2013 Quick Reference

Writing a Macro, to find and replace in a Macro ;)

Hey guys,

First of all, i wanted to thank you all in advance. I've actually been scrounging information off this for quite some time. You've helped me overcome a vast number of problems i've encountered.

This one though, i couldn't find anything on, and i thought it was about time i asked you directly

I'm not very good at explaining, so bear with me.

I have 9 Excel workbooks. 1 of these is a Final sheet, the presentation sheet that get's printed and given out. The other 8 are "raw data" sheets where i've done all the calculations and made the tables.

The Raw Data workbooks are given to me each month and dropped in a folder, overwriting the previous "raw data" workbooks. Trouble is, these workbooks have a Month name in the filename. This month name is important and i cant remove it. Example:

Final.xls, 101MAY.xls, 102MAY.xls, 103MAY.xls, 104MAY.xls, 105MAY.xls, 106.MAY.xls, 107MAY.xls and 108MAY.xls.

(These would overwrite 101MAR.xls, 102MAR.xls, 103MAR.xls etc... Final.xls does not get replaced)

I've written a macro in Final.xls that copies and pastes all the relevent information from each Raw Data sheet into the corresponding Tab in Final.xls. Thus:

Code:
    Workbooks.Open Filename:= _
        "J:xxxxxxxxxxxxxxxxRaw_Data101MAY.xls"
    Sheets("Reports").Select
    Cells.Select
    Selection.Copy
    Windows("Final.xls").Activate
    Sheets("101Data").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Windows("101MAY.xls").Activate
    ActiveWorkbook.Close (False)
Edit: sorry, i should have mentioned, this is replecated 8 times, once for each raw data workbook.

As you can see, this does a Copy/Paste of the entire sheet from one workbook, into the Final.xls. Trouble is, this macro would obviously not work after i've overwritten the May files with June files (101JUN.xls etc).

What i thought i could do is make a cell in Final.xls with a drop down box, with the months Jan - Dec inside. Then i could create a second macro, that will run a Find and Replace inside the First macro, using whichever entry in the drop down box is selected to Find/Replace the file names in the macro.

IE:

Drop down box; select June.
Run new macro
**New macro would need something like "If June, then use 101Jun.xls... If August then use 101Aug.xls**
New Macro finds/replaces 101MAY.xls with 101JUN.xls inside the old macro
Run old macro
Macro copies and pastes data from 101JUN.xls like before.

Does that make sense Really sorry i made it so long winded. I'm terrible at explaining things. If anyone can help, i'd be so grateful. I can obviously just do a manual find and replace in the macro, but i'm trying to make this task run as automatic as possible, with as little human interaction as i can!!

Really greatful. thanks guys...


Post your answer or comment

comments powered by Disqus
Hi,

I'm trying to write a macro to find and replace and currently use:

For Each cell In Selection
cell.Value = Replace(cell.Value, “January”, "February", 1, 1, vbTextCompare)
Next cell

However, using this code means that the find and replace values must remain constant. Could anybody show me how to do the same thing but instead of finding "January" as constant text, I want it to look at the range "LIST" find the text entered in cell C5 and replace it with the text entered in C6.

Does that make sense and sound do-able to anyone???

Thanks for your help!

I have recorded a macro to find and replace all the dates but when I run it
it doesn't actually change them. I want to change all the dates so that they
are all the 1st of the the month instead of specifying the day. I can't think
of an easier way of doing it so if anyone has any better ideas it'll be very
appreciated!!!

Here's the code:

Columns("U:U").Select
Selection.Replace What:="??/12/2005", Replacement:="01/12/2005",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="??/01/2006", Replacement:="01/01/2006",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="??/02/2006", Replacement:="01/02/2006",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="??/03/2006", Replacement:="01/03/2006",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False

It doesn't come up with an error, it just doesn't do it!

Thanks in advance!!!!

Help,
I used the below code (kindly supplied by PetLahev on this site) as a find and replace macro. It replaces the text "qu1" with "Changes Text" through out my powerpoint presentation. The code works fine in ppt2007 but not ppt2003. Half way through the code is the line; Set oTxtRng = oShp.TextFrame.TextRange and this fouls up with the message "Invalid request. This type of shape cannot have a TextRange". Help, how can I fix the code for ppt2003?
Cheers,
BennyJ


	VB:
	
 ReplaceText() 
    Dim oSld As Slide 
    Dim oShp As Shape 
    Dim oTxtRng As TextRange 
    Dim oTmpRng As TextRange 
    Dim strWhatReplace As String, strReplaceText As String 
     
     ' write find text
    strWhatReplace = "qu1" 
     ' write change text
    strReplaceText = "Changes Text" 
     
     ' go during each slides
    For Each oSld In ActivePresentation.Slides 
         ' go during each shapes and textRanges
        For Each oShp In oSld.Shapes 
             ' replace in TextFrame
            Set oTxtRng = oShp.TextFrame.TextRange 
            Set oTmpRng = oTxtRng.Replace( _ 
            FindWhat:=strWhatReplace, _ 
            Replacewhat:=strReplaceText, _ 
            WholeWords:=True) 
             
            Do While Not oTmpRng Is Nothing 
                 
                Set oTxtRng = oTxtRng.Characters _ 
                (oTmpRng.Start + oTmpRng.Length, oTxtRng.Length) 
                Set oTmpRng = oTxtRng.Replace( _ 
                FindWhat:=strWhatReplace, _ 
                Replacewhat:=strReplaceText, _ 
                WholeWords:=True) 
            Loop 
        Next oShp 
    Next oSld 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Help me please...
I am trying to program my first ever macro in powerpoint 2003 / 2007. I don't think what I want to do is complicated and could really do with a finished bit of working code to paste into visual basic. I am a novice and the help pages and many hours of scouring the internet has come to no answer.

I want the macro to find the text "qu1" which is featured 17 times in the presentation and replace it with text entered from a dialogue box.

Cheers for any help,
John

Ya'll will be glad to know that I am almost finished with that "project". I need to know how to do one thing though.

How would I write a macro to find duplicate dates in a column and then copy certain data from one of the duplicate dates' row, paste it on the other duplicate date and delete the line it just finished copying from. I know that made a lot of sense. I attached an example that shows what exist and what it should look like after. Here is a simple example

B

03/11/2003 07:00 43 34
03/11/2003 07:00 --- --- 45
03/11/2003 13:00 12 34
03/11/2003 18:30 24 57
03/11/2003 18:30 --- --- 67
03/12/2003 00:30 35 23

The dates are in column B
The data are in columns C, D, E, respectively

The macro should recognize the duplicate dates (03/11/2003 07:00) and copy the data from the bottom duplicate date (03/11/2003 07:00), which is "45"

It should then paste this number in the cell directly above it

Then it should delete the line it just copied from.

It should then recognize the other duplicate date (03/11/2003 18:30) and do the same procedure

When finished it should look like

B

03/11/2003 07:00 43 34 45
03/11/2003 13:00 12 34
03/11/2003 18:30 24 57 67
03/12/2003 00:30 35 23

Is this possible? and if so, could someone please help me out!

Thanks

Here's a better representation of what the before looks like and the after should look like.

Hi,

Is it possible to run a macro to find and replace text in another Macro?

Module 1 is currently pointing to several files that end in the word July (all the files are in same folder).
I want Module 2 to open up VBA and search Module 1 for the word July and replace with August. This way I'll run Module 2 first, then Module 1.

Thanks!

I have a folder of Excel files. Is there a macro to find and replace a string in every excel file?

I have a huge mailing list and I have a macro to find and replace certain critera, the goal is to seperate these addresses into 2 parts.
the street address in ADDRESS1 and the apt/suites in ADDRESS2, If it doesnt have a apt/suite # then it should be blank.

What I want to do is find "* st" and as long as "st" is the end of the string then replace with ""

for example:

A1) 526 West 26th Street, Suite 920
A2) 526 W. 26th St. Ste 920
A3) 253 W. 23th St
A4) 235 34th st Apt 3
A5) 343 lafayette st

so that after I run the macro I have in my ADDRESS2 the following:

B1)Suite 920
B2)Ste 920
B3)
B4)Apt 3
B5)

If I do a simple Find and replace of "*st" I come up with the problem of replacing parts of A2 and
im left with "e 920" which is not what I want.
I want to be able to create multiple search criterias and not just those that have " st" at the end of the
string.
Does anyone know when using a Find and Replace if there is a special character to indicate that its the end of the string?

A3) 253 W. 23th St
that way i can Find " st(Special Character to indicate end of string)" replace with ""

thanks.

I have written a macro to find and replace various characters in a
spreadsheet. I cannot figure out how to get it to find a " and replace it
with nothing. All the rest of the find and replaces work. It is near the end.
Please look as I need help.

Range("A1").Select
Cells.Replace What:=">>", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False
Cells.Replace What:="[", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="]", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="(", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:=")", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="#", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="/", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="-", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:=",", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:=".", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="'", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="~"", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="~?", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False
End Sub

I have written a macro to find and replace various characters in a
spreadsheet. I cannot figure out how to get it to find a " and replace it
with nothing. All the rest of the find and replaces work. It is near the end.
Please look as I need help.

Range("A1").Select
Cells.Replace What:=">>", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False
Cells.Replace What:="[", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="]", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="(", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:=")", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="#", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="/", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="-", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:=",", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:=".", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="'", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="~"", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="~?", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False
End Sub

I heed help with the attached work book THE HOOVER.xlsm

,.. I need a macro to do the following in the sequence below,..

Replace all Commas ( , ), Quotes ( " ) and Slashes ( / ) in Sheet 1 with a space (" ")

Then

Convert everythin in column K to Uppercase

then

Run my old macro

Then

Find and replace the following words in Column K

Find STERILE, replace with STER
Find YELLOW, replace with YEL
Find BLACK, replace with BLK
Find BLUE, replace with BLU

Then

count the characters in each each of Column K and put the value of the character count in corresponding cell in Column L

Then

Copy coloured cell in column K and paste with format to corresponding sheets

red cells in Colum K will go to Sheet tagged Dirty Output Cell B2 down
Green Cells in Columnk K to Sheet Tagged Clean Output Cell B2 down

Hi. I need a way (can be a macro) to find duplicate words in a column, and then to copy those duplicated values to another column.

An example. My column looks like this:

Column
Row1 - John Cage / Johnny Bravo / Rambo / Indiana Jones / Neo
Row2 - Frodo / Bruce Lee / Steve / Harry / John Cage

I need to find every single duplicate name, and then to copy them to another column. Problem is, I can't seem to find a macro that does that - since the macros I have look only for exact values in the rows, they don't recognize as duplicate anything in a row that simply has a partial identification with another row. For example, the macro I have does not recognize as duplicate the "John Cage"s in those two rows - because their respective rows aren't exactly equal, only two words in them are - the "John Cage"s. But I need to find those "John Cage"s.

Is there a solution for my problem?
Thank you for any advice you may be able to offer.
-Bruno

Hi

I need a macro to find any cells containing external data links within a workbook which are written with the universal naming convention, e.g.

='myservermyshare[source.xls]Prices'!B5

and replace it with the letter drive, e.g.

='G:[source.xls]Prices'!B5

Just as background info, there are a few hundred workbooks that need to be checked and changed, so Find and Replace is taking too long.

Also the drive letter is always the same on all PCs on the network.

Now I'm working on a macro to find and replace column headers in a
spreadsheet that the raw data I use in XL comes from. I've only got a
week left on my contract with the state which may be enough time to get
another set of data out to the counties. (Have applied for a full time
job here, so I might be back.)
I have 2 columns of data, starting at B40, in one workbook, call it
ABC. Column B contains the Source document headers I want to replace in
the other workbook, and Column C has the headers I want to replace them
with. I want to select the downloaded data worksheet, then run the
macro to go the the other workbook, get the old header in cell B40, try
to find it in row 1 in the downloaded datasheet and replace it with the
text in C40. Then go to B41, and repeat the procedure until it hits a
blank cell in column B. If it fails to find the text from column B,
just continue with the next cell in the column.
This will make reformatting the incoming datasheets much quicker, so I
can then copy them into the existing workbook with the new quarter's
data.
Can anyone point me in the right direction as to how to set this up? I
appreciate the help.

im trying to create a macro to find and replace the following thru mulitple worksheets. NJ-1,NJ02,NJ01 and NJH102 Which is located in coloumn E. I want them all changed to just say NJ.

Hell all,

Attached is an example of the file that I am working with. What I want to do is create a macro to find and replace what is in Sheet1 "Zone" column with the corresponding value in GeneralZones column B.

If Sheet1 "Zone" has a Z001 in it, I would like it replaced with the corresponding value associated with Z001 in the GeneralZones Sheet (in this case, "1st floor").

Any help is always appreciated! Thanks in advance.

Test.xlsm

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

Hello,

I am writing a macro to find and trim the last 8 characters of cell contents that end with "_" plus a 7 digit number (i.e., IC4_M13F_3052823). I am new to this and not quite finding the way to use the # wildcard to do the trick.
Here is my code:

Any help would be appreciated!

Hi, i have a very large excel document which needs to grab values for each year for a number of countries!

currently i have the same formulae across the sheet and have created a macro to find and replace selected areas to the correct formula.

however, because every time the sheet is changed the links update it takes waaay to long... will take days

is there a way to prevent this?

thanks

Jonathan

I currently use the following within a macro:

Range("B7:B100").Select
Application.ReplaceFormat.Interior.ColorIndex = 3
Selection.Replace What:="sutherland", Replacement:="Sutherland",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=True

I am not sure if this is the best way to accomplish what I need....but it
works. It looks for sutherland, finds it, and basicly shades the cell with
colorindex=3. Question is....Can I somehow substitute a cell reference in the
What:="xxx" area. I would like the macro to search the range for a match to a
specific cell and then replace with that cells value and color code the cell.

Thanks,

Ken

Hi all

Can anybody help?

I have created very basic macros in Excel but have no knowledge whatsoever
of Visual Basic. Is it possible to create a macro that will search for a
particular word(s) in the whole of an Excel workbook and replace it with
another? Would I simply go into Record mode and select Edit, Find & Replace
and enter the text? How would this be applied to the whole workbook rather
than the active sheet?

Any help would be appreciated.

Thank you

Louise

Hi all

Can anybody help?

I have created very basic macros in Excel but have no knowledge whatsoever
of Visual Basic. Is it possible to create a macro that will search for a
particular word(s) in the whole of an Excel workbook and replace it with
another? Would I simply go into Record mode and select Edit, Find & Replace
and enter the text? How would this be applied to the whole workbook rather
than the active sheet?

Any help would be appreciated.

Thank you

Louise

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

Hi,

I'm looking for help writing a macro (to be run using a button on worksheet "Src" in the attached book "Sample").

The short version... is I need to search for a worksheet in the book (using criteria), make a copy of that worksheet (and name it using criteria), place the tab immediately before (to the left of) the worksheet it copied, and then change the contents of a bunch of cells.

If it is more feasible to help me using the short version, that will still be very helpful. Otherwise, the "long version" is a little more detailed in what I ultimately need to do.

Long version...

This is for an excel tool being developed for one user (I'll call him "user").

Before the user runs the macro, he is to write the intended ISO WEEK (a number 1-53) to Src!E5 (I'm not calculating it using the date NOW() because I specfically need him to enter it) as well as the current year to Src!H5. (Typically, he is only increasing the existing WK number by one.)

When he runs the macro, I need the following to be performed:

1.) Find the worksheet (in the same workbook) for the previous week.

(If 46 and 2011 were entered, the worksheet for the previous week would be "WK 45-11". If worksheet "WK 45-11" doesn't exist, "previous week" needs to count downward until a match is found. If the search crosses from the current year to the previous year, I have a function I wrote that determines if the previous year has 52 or 53 ISO weeks in it--see 1a. Ensures I'm not accidentally skipping WK 53 when it exists.)

1a.)

=(((IF(WEEKDAY(DATE(Src!H5,1,1),2)<5,WORKDAY(DATE(Src!H5,1,1),-WEEKDAY(DATE(Src!H5,1,1),2)+1),WORKDAY(DATE(Src!H5,1,1),1))+3)-(IF(WEEKDAY(DATE(Src!H5-1,1,1),2)<5,WORKDAY(DATE(Src!H5-1,1,1),-WEEKDAY(DATE(Src!H5-1,1,1),2)+1),WORKDAY(DATE(Src!H5-1,1,1),1))+3))/7)

Where Src!H5 is the current 4-digit year entered in Src!H5. The function returns the ISO WK number of the last week in the previous ISO year. If Src!H5 is 2016, the function returns 53 because 2015 has 53 ISO weeks in it.

2.) create a copy of the "previous week" worksheet (which ever is the last week found), name the copy "WK 46-11" (If 46 and 2011 were entered in Src!E5 and Src!H5), and place the tab immediately before (to the left of) the "previous week" tab.

3.) On the new sheet ( "WK 46-11" ):

Replace the contents of cell 'WK 46-11'!B2 with the result of Function 1 (I can post it if interested--otherwise something simple like 1+1 is fine for now), replace the contents of 'WK 46-11'!B3 with the value zero, and replace the contents of 'WK 46-11'!I3 with the text "HH:MM".

4.) On sheets: S1, S2 and S3

Replace the contents of cell L2 on all of these sheets (there are actually 42 sheets, but once I see how I need to do 3 using those names, I can adapt it) using the value in Src!E5.

5.) On sheet DASHBOARD:

Replace the contents of DASHBOARD!A1 using the value in Src!E5, and replace the contents of DASHBOARD!A21 using the value in Src!H5.

And that's it. I have an advanced aptitude for writing functions in cells, but I haven't done it as much in VBA, and haven't actually done a whole lot in VBA yet so I need to start learning as I go.

If that's too much info or work for one question, I'll break it up and try getting help that way.

Or, if you can only structure some tips to help me along, that's also great.

Thanks! I will really apprecaite the help.


No luck finding an answer? You could always try Google.