Free Microsoft Excel 2013 Quick Reference

Using variable with FIND - REPLACE


A total novice at this so any help would be good. I have the following in a macro:
    Selection.Replace What:="200703", Replacement:="2007-03-", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    Selection.NumberFormat = "d/m/yy;@"#
I have 2 varibales called date1 and date2, which I am using a msg box for the user to enter the find and replace values. Can I use the var names in the code to replace the 200703 (date1) and the 2007-03- (date2)? If so what is the code, I have used & varname before but this doesn't work, help - please.

Why am I doing this I hear you cry ! Well the cell value is 20070325. I need to format the cell as a date 2007-03-25. If you try the normal format cell with date or a custom yyyy/mm/dd, it returns a series of ######. If I use the FIND - REPLACE it will let me format it once that has been done.

Ta much.


Post your answer or comment

comments powered by Disqus
I I'm trying to use Macro for Find/Replace values in different worksheets on the same column.
Here is the example of the code:

    Selection.Replace What:=";3;FALSE", Replacement:=";5;FALSE", LookAt:= _ 
    xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ 
    Selection.Replace What:=";2;FALSE", Replacement:=";3;FALSE", LookAt:= _ 
    xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ 
    Selection.Replace What:=";4;FALSE", Replacement:=";6;FALSE", LookAt:= _ 
    xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ 
Next WS 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The values that I want to replace are i cells with formulas like:
=IF(ISERROR(VLOOKUP(A371;'C:Lokalni Magacini STTMaster[Lokalni magacini Prosek.xls]Pivot'!$A:$C;2;FALSE));0;VLOOKUP(A371;'C:Lokalni Magacini STTMaster[Lokalni magacini Prosek.xls]Pivot'!$A:$C;2;FALSE))

Can someone help me?
Thanks in advance!

I am trying to find and replace repeated data in a column with the numbers 1, 2, 3, 4, 5 etc. sequentially. I am thinking the solution is along the lines of :

Dim a variable (1);
find&replace 1st instance using the variable;
increment variable by 1;
find&replace 2nd instance using the variable; etc.

but can't quite get to grips with how to code it.
Any help would be most welcome.

I use macros to find and replace names. Each month I copy the file with the find/replace macros to a new file name. The new file may or may not have some of the names fournd in the previous month's file. When I run the macro in the new file it finds and replaces names as it should. When it can't find a name, the macro stops running and gives an error message. How can I tell the macro if it doesn't find the name specified, find the next name and replace with specified name. I want the macro to go through the entire file, finding/replacing or skipping and going to the next name when a name isn't found.

Desperately need help!

I have an excel sheet with VBA, and it has time data like the following:

10:00A - 12:15P

For now I have a Find/Replace macro that does the following:
Cells.Replace What:="0P", Replacement:="0 PM", LookAt:=xlPart
Cells.Replace What:="0A", Replacement:="0 AM", LookAt:=xlPart
Cells.Replace What:="5A", Replacement:="5 AM", LookAt:=xlPart
Cells.Replace What:="5P", Replacement:="5 PM", LookAt:=xlPart
And then another Sub that goes through and splits it into two cells. What I'd like is Find/Replace VB Code that would look for either a 5 or a 0 AND for a A or P, and then I would go from 4 lines to one line. Like:

But all of my attempts to use Arrays keep getting errors.

Can someone help?

Hi guys/gals,

Does anyone know of some generic VBA code that will perform a find/replace? I need to search through about 500+ cells (all in one column) for a string of text and replace it with another string of text. Each cell contains about 12,000+ characters (with spaces), so the contents of each cell are rather large.

I've tried using the basic find/replace command via the edit menu, but it gives me the error msg: Formula is too long. Which is why I was hoping one of you might know some VBA code to fix this

If so, let's just say that it's column D that I'm working on and the text string I want to replace is "Happy Birthday" and replace it with "Merry Christmas".

If you have spare time, PLEASE HELP!

Thanks so much and kindest regards,

I have recently switched to 2003, and noticed som issure with FIND/REPLACE. First off, after I use it once, from then on it starts my cursor in the Replace box. Usually when I do a F/R I want to do something new, not find the same thing. Any way to change this? Also, sometimes it pops up with the box checked for Find Entire Cells. I usually don't want this. What is causing it to get checked automatically?

Is there a way to change the search within feild in the replace command from
"sheet" to "workbook"?

ie something like:

Cells.Replace What:="a", Replacement:="b", Within:=xlSheet

The within option in replace doesn't seem to be covered in VB Cells.Replace
yet Cells.Replace is effected by what ever the Within option is set to using
the normal find/replace command in excel.

The only other idea I have come up with is using sendkeys but sendkeys may
not be very robust.

In previous versions of Excel, I could easily manipulate links by using
the copy/paste and find/replace functionality.

For example, I have the link
='[07-05ApprovedAPSourcingDecisionsWithSQData.xls]Charts by
CommData'!$B15 in one cell and the link
='[08-05ApprovedAPSourcingDecisionsWithSQData.xls]Charts by
CommData'!$B15 in the one below. As you can see, the link is changing
the file name with the reference to the same cell in each file (I have
a file for each of the 12 months). I copy and paste that to the next
column and it prompts me for the file location of each cell!!! I
thought that maybe I need to open the files, but it still prompts me.
Once I go through all of the locations to get a new column of links, I
then need to change the $B15 reference to $B16. I tried to use a
simple find/replace and I am stuck selecting the file locations for
each change AGAIN. This is NOT efficient at all!

Is there a setting that can fix this? I just recently upgraded to
Office 2003, so I may be ignorant on this...

Any suggestions are greatly appreciated!!

Hi All,

My spreadsheet contains bank account numbers that contain "-" and spaces. To get rid of these characters I use Edit > Find > Replace.

When I do this the last digit of the 16 digit number changes to a zero rather than show the correct number. The first 15 digits of the account number are fine. Its just the last digit that seems to be affected.

Any ideas?


I'm doing a search simular to this


and I want it replaced with this


HOWEVER when I do the find & replace I don't want it to change whatever the current value is to D3 I want it to stay as whatever it was how do I exempt the "D" value from the search??

Hi all,

New here on the forum and new with macro so please be patient with me.

I'm trying to find a macro that would do the following:
1. Find a text in a range of cells in a different sheet (text that I search changes all the time, but the range is always the same)
2. when it finds the text, it should replace a cell in that row with a number from a specific cell

I was try to do it myself for 2 days now and no success. Does anyone have some macro that does this or a manual with useful commands (like find, replace, and so on..)

thx in advance.

Im trying to use variables with different modules but i keep running into errors. The error i usually get is a "compile error: Expected variable or procedure, Not module".Ive made the variables public and ive tried passing the variables but im not sure if im coding it the right way. When you pass variables do you need to code it like this?

Public variable1 as single, variable2 as single, variable3 as single

Public sub FirstSub
Variable1 = 1
Variable2 = 2
Variable3 = 3

call NextSub(variable1, variable2, variable3...)
End sub

and then in the next subroutine in the other module(Nextsub) would i need to pass the variables like this?

Public NextSub(variable1, variable2, variable3)

end sub



Will someone show me the correct syntax for using variables when opening workbooks.

I want to open workbook "Main" but by refering to "book" which equals "Main"

Dim wbook As Workbook 
book = "Main" 
Set wBook = Workbooks.Open("C:Documents and_ SettingsdefaultDesktopbook.xls") 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I hope you understand what I am trying to do.


Hi everyone!

I use the Excel Find/Replace dialogue box frequently.

However, when the dialogue box is displayed on the screen, all functionality of the spreadsheet is temporarily frozen, and I can only access the Find/Replace box.

Someone at my old work got the Find/Replace box to remain accessible on the screen, while at the same time I still had full navigation of the spreadsheet (for editing, scrolling etc.).

I have been trying to figure out how to do this for a long time.

Does anyone know how to do this?

Thanks for reading
BTW, I am using Excel 2003.



I just selected an entire worksheet and used the edit/Find-Replace function. Unfortunately, I typed in the wrong "Replace" data, and now it is looking for a file that does not exist. Normally I would just hit Cancel until it scrolled through all the selected cells, but this one has literally 30,000 "finds" to replace!

Is there another way to quickly exit this function?


In a column, I have a series of numbers in this format: 00700*, 01200*,
127000*. This data comes from an outside source, and I need the cells to
read 007, 012, 127, without the final 000*.

I can't seem to make this happen with Find/Replace, finding 000* and
replacing with nothing (to remove the final 000*, as I can do in Word).

Any thoughts how I might accomplish this?

Kind regards to all.

I have a pivot table which I would like to control from a navigation sheet
i.e. I'd like to be able to change the values in the pivot using variables.
Is this possible?

I have several hundred part descriptions consisting of a generic name and details, separated by a comma. However, sometimes only the generic name exists. I need a separate list of only the generic names.

I am trying to use IF with FIND in case that the comma does not exist within the description.

Problem is, when the 'error' condition occurs; i.e., the comma does not exist, the overall result is #VALUE. If the comma does exists, then everything
works fine.

I have tried several versions, including the following, but none have worked:
" =IF(FIND(",",C6)="#VALUE!",C6,LEFT(C6,(FIND(",",C6))-1)) "
Where the description is in C6, and if there is no comma, then the entire cell contents is the generic name I want.

What is the proper syntax to handle FIND-resulting-in-error condition?




I have a number of named ranges that I need to find/replace data in.

For example, in range "PAPKGC" there are three variables: F, P, and X. For "F", the cell needs to contain the word "Flat". "P" is to contain "Package" and "X" is to be blank. Range "PASIDE" also contains 2 variables. I am to concatenate any cells with a value of 2 with "/pk" and any cells with a value of 1 are to be blank.

So my big question is: How can I use the named ranges to find and replace this data using a VBA macro?

This is what I'm trying, but I have no idea what I'm doing.
Sub ReplaceText()
    Dim rngData As Range, rngCell As Range
    With ThisWorkbook
        Set rngData = Range("PAPKGC")
    End With
     'define the data range to evaluate
    For Each rngCell In rngData
        Select Case rngCell.Value
            Case "F"
                cell.Value = "Flat"
            Case "P"
                cell.Value = "Package"
        End Select
    Next rngCell
End Sub
Any help is appreciated!


I have an excel spreadsheet where I'm using macros to find dept numbers and replace them with macro names. The macro works well until it can't find a department number. Then I get the error message "Run Time Error 91"
"object variable or with block not set". How can I get the macro to go find the next department number when it doesn't finds that the current one doesn't exist?
A portion of the macro is shown below.

Sub Convert_dept_Nos()
' Convert_dept_Nos Macro
' Macro recorded 12/14/2006 by Guilford County

ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Selection.Find(What:="101", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
Selection.Replace What:="101", Replacement:="COUNTY COMMISSIONERS", LookAt _
:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Find(What:="102", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _


Currently I undertake this process using the 'Kickbutt VBA Find Function' with a seperate command line for each of the 100+ records.

I am not sure of how exactly to go about doing this, but think I need to load the data into an array (sorry limited knowledge).

On Sheet1, Column A I have a table of text with abbeviations contained in Column B (100+ rows).

On sheet2 I have a table with 1,000 + rows. In Column C I have long text strings, these contain an element of the strings that are represented on Sheet1 together with other superfluous text.

I would like to replace all the items in Column C with the abbreviated form and be able to switch back (and would prefer not to have to hard code it all again).

I attach an example of the workbook.

Thanks in advance


Is possible with code and a loop (because i want to put in a module between different steps) find and replace different text???

See my example below... i only have the column A, and i want to make column A as the result that you can see in column C (note that column C, in the real sheet doesn't exist, here i'm showing the result i want to get). So, find the different words in column A, and replace it in the same column A...

I know that is easier using Find/Replace in excel, but i need this with code, please :$

This should be incredibly easy to do, but for some reason it is not working for me.

I have a column of values and formulas highlighted. I want to leave the formulas and remove all of the numbers from the cells in that column and replace them with zeroes.

I've tried using Find/Replace, using >0 in the FIND box, but Excel cannot find any values.

I have also made sure that the sheet is not protected.

Any ideas??



Hi Folks,

I am looking for a way to get rid of certain numbers/text in a cell using find and replace box (Cntr H) only.

I want to get rid of anything before the # sign. So, I will do the following in the find&Replace. In the find box i will type *# . In the replace box I will leave it empty.
By doing so I will get rid of 1111 and will have 34952369 only.

Now, I want to do the same thing with the following...
If I do the same thing I did above i.e. ---> ** it deletes all the numbers in the cell. Just for fun I tried typing ~* and *) to see if anything works but all went invain.

My question is does anybody know if there is a way to get only 34952369 in the cell by strictly using find/replace box only in a situation when the criteria character is asterisk.

thx to all in advance.

Note: my Q is purely for learning purposes. Just to know if somebody out there knows a trick besides this there is nothing that I am trying to accomplish here.

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