Search and replace macro

Microsoft corporation Office 2010 profes...
January 12, 2015 at 20:57:05
Specs: Windows 7
Hello all

Does anyone have a macro for Excel that can do the following:

I have some text strings in column A like this: ";B" and "(B" and "BV" and so on.
I then have some text strings in column B like this: ";$B$" and "($B$" and "$BV$ and so on.
There is the same number of strings in each column.

What I need the macro to do, is if I select a range and then run the macro, it should search for those strings in column A and replace with the corresponding value from column B, (the same row) in the selected range of cells.

The selected range of cells has formulas in them.

Thanks in advance

/Jacob


See More: Search and replace macro

Report •


#1
January 13, 2015 at 13:12:41
Let's start by making sure that we are using the correct terminology.

First you said:

"I have some text strings in column A like this:..."

Then you said:

"The selected range of cells has formulas in them."

What "range of cells" are you selecting? If that range is in Column A, then the cells can't contain text strings because you said they contain formulas. A formula is not a text string - you can proof that to yourself by trying to apply any of the text functions (MID, LEN, etc.) to those cells. They will return values based on what is displayed in the cell, not the formula itself.

OK, so let's assume you have formulas in Column A and text strings in Column B. Could you please post an example of a formula that has a ;B in it as well as one that has a (B in it?

Better yet, please post some examples of the formulas in Column A and the text strings in Column B that so that I can get a better idea of what you are working with.

Be sure to use the pre tags so that your data is easier to read.

e.g.

      A              B     
1  =A1+B100         $B$
2    etc.           etc.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
January 13, 2015 at 14:11:43
	A	B	C	D	E	F	G	H	I	J	K	L
1	1	2	3	4	5		SAND				A	$A$
2	1	2	3	4	5		SAND				B	$B$
3	1	2	3	4	5		SAND				(C	($C$
4	1	2	3	4	5		SAND				D	$D$
5	1	2	3	4	5		SAND				;E	;$E$
6	1	2	3	4	5		SAND				F	$F$
7	1	2	3	4	5		SAND				";G	";$G$
8	1	2	3	4	5		SAND				AA	$AA$
9	1	2	3	4	5		SAND				AB	$AB$
10	1	2	3	4	5		SAND				AC	$AC$
11	1	2	3	4	5		SAND					
12	1	2	3	4	5		SAND					
13	1	2	3	4	5		SAND					
14	1	2	3	4	5		SAND					
15	1	2	3	4	5		SAND					

SAND is Danish for TRUE

Formula contain behind the TRUE is:

=OR(A1;B1;AND(C2=0;D20<>"");D2=0;E4=5;F1<>"";G99="";AA1=0;AB3=AC3)

The point is, that I want two columns, column K with WHAT/FIND/MATCH strings and column L with replacement strings.

Hope that clearifies Things, else just ask Again and I will try to elaborate.


Report •

#3
January 13, 2015 at 14:24:52
I have tried myself to get an idea of how this could be done and have recorded a macro and edited it a bit. The problem is tho, that I can't get this workingen:

Sub FindAndReplace()

    Columns("G:G").Select
    Selection.Replace What:=";AB", Replacement:=";$AB$", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

But this Works fine:

Sub FindAndReplace()

    Columns("G:G").Select
    Selection.Replace What:="AB", Replacement:="$AB$", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

Notice the semi colons before the AB and $AB$. If they are there, it doesnt substitute. If I do the search (the one I recorded the macro with) it replaces just as it should. What have I forgotten, that makes the semi colons stop the replacement?

btw: I am getting more and more interested in VBA and if I should guess a solution for this, I would say that a For Each function is needed and the ranges of those two columns (WHAT and replacement) needs to be defined and then the code has to cycle through every cell in the selection, check the Whole range of WHAT's and replace those found with the replacement strings of columns L. How to put all this into a code is beyond my knowledge atm, but I am eager to learn more.

message edited by JacobJ


Report •

Related Solutions

#4
January 13, 2015 at 17:29:15
Are you trying to convert an entire range of cells from Relative to Absolute? If so, take a look at this site:

http://www.mrexcel.com/forum/excel-...

There are a couple of suggested add-ins as well as a piece of code that will accomplish that goal. If that is not what you are trying to do, then please explain exactly what the final goal is.

I was going to suggest that you use VBA to write your formulas as opposed to trying to change what you already have, but you only provided a single formula so don't know if there is a pattern that VBA can use to create sequential formulas. Based on the single example you posted, this code will produce the 3 sequential formulas listed:

=OR(A1;B1;AND(C2=0;D20<>"");D2=0;E4=5;F1<>"";G99="";AA1=0;AB3=AC3)
=OR(A2;B2;AND(C3=0;D21<>"");D3=0;E5=5;F2<>"";G100="";AA2=0;AB4=AC4)
=OR(A3;B3;AND(C4=0;D22<>""),D4=0;E6=5;F3<>"";G101="";AA3=0;AB5=AC5)

Sub myFormulas()
  For rw = 1 To 3
    Range("G" & rw).Formula = _
      "=OR(A" & rw & ";B" & rw & ";AND(C" & rw + 1 & _
      "=0;D" & rw + 19 & "<>"""");D" & rw + 1 & "=0;E" & _
      rw + 3 & "=5;F" & rw & "<>"""";G" & rw + 98 & _
      "="""";AA" & rw & "=0;AB" & rw + 2 & "=AC" & rw + 2 & ")"
  Next
End Sub

Or with absolute references...

Sub myFormulas()
  For rw = 1 To 3
    Range("G" & rw).Formula = _
      "=OR($A$" & rw & ";$B$" & rw & ";AND($C$" & rw + 1 & _
      "=0;$D$" & rw + 19 & "<>"""");$D$" & rw + 1 & "=0;$E$" & _
      rw + 3 & "=5;$F$" & rw & "<>"""";$G$" & rw + 98 & _
      "="""";$AA$" & rw & "=0;$AB$" & rw + 2 & "=$AC$" & rw + 2 & ")"
  Next
End Sub

The technique is to concatenate the text portions (in quotes) with the variable. When using actual quotes in the formula e.g. =IF(A1<>"",1,2) you must enclosed the quotes in quotes even within the main quotes.

"=IF(A1<>"""",1,2)"

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
January 13, 2015 at 20:41:14
To your question about changing a whole range from relative to absolute, the answer is yes AND no.

Yes because there are like 40 references in the formula that needs to be with absolute reference and 10 that has to be with relative references.

I have tried different macros that uses some build in functions to just change all references to absolute, but they arent working for me with formulas that big. I get the #VALUE! error.

So at the moment I at doing a find and search for each cell reference, starting with the double charater columns like AA, AB, AC. It is very time consuming to make changes to those formulas because I have to go through all those find and search exercises to make it the way I want.

What I would like to achieve with this thread, was for me to have a relative referenced backup formula, that I could make the changes to, insert in the row I needed. Fill it down and then afterwards run a macro, that would do all those find and search exercises for me.

Search for the WHAT values in each formula in a range of formulas and then replace with the corresponding replacement values from the other column.

But maybe that way of doing it is impossible?

btw. your concanate suggestion will also be very time consuming for me to do, because I have so many formulas in that range, then the manual find and replace methode I am using now is easier.

message edited by JacobJ


Report •

#6
January 14, 2015 at 08:15:20
Let me start by saying that working on your issue is a bit of a pain because your formulas use semi-colons as opposed to commas. Therefore everything that you post has to be edited to use commas so that I can test it. Then, being the nice guy that I am <g> I have to edit it all back to use semi-colons before posting my response.

re: "I have tried different macros that uses some build in functions to just change all references to absolute, but they aren't working for me with formulas that big. I get the #VALUE! error."

This still makes no sense to me and without your actual workbook I have no way of figuring out why that is happening. If you would be willing to send me a copy of your workbook I might be able to figure something out. I would request that condense your workbook into a file that contains no confidential information and enough formulas and data such that problem is evident. I would then be working on the actual issue, not trying to deal with things like this:

"I have some text strings in column A like this: ";B" and "(B" and "BV" and so on.
I then have some text strings in column B like this: ";$B$" and "($B$" and "$BV$ and so on."

We know that that is not what you actually have, because the example you posted has that data in K & L, with formulas in G. My point is simply that I'm not working on the actual issue and it's getting us nowhere especially considering that I have to build my own spreadsheets for testing purposes. I seriously doubt that my test sheets are similar to yours since I don't have "like 40 references in the formula". This is just too complicated of a issue for us to continue trying to solve in the manner we are currently using.

If you can upload an actual file to filedropper or dropbox and post a link back here, I can take a look at it. Otherwise I think we are just spinning our wheels. If you can not upload a file, I can send you an email address via PM. The file should not only contain the formulas that you need to change, but also an example(s) of what the end result should be so that there is no confusion as to what the output should look like. Without an actual file to work with, I am going to respectfully pass on trying to resolve this issue.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
January 14, 2015 at 21:10:53
I can see your point and I can totally understand it. There are some confidential issues involving this worksheet. I think it wouldnt be that big an issue, if the actual workbook is posted, but I am on the safe site of things, so I will not "gamble" my new job, by posting things that my boss would consider confidential work. On the other site I need some of those improvements that you and others on this forum have helped me with and until now a lot of those issues could be solved by making a fakesheet that was constructed the same way, but would have no obvious similarities to the actual workbook we have at work.

When I get the time and after accept from my boss, I will try to take a copy of the workbook, change all names and stuff like that, so the only thing left is the fomulas and structure of the workbook and email it to you, so you can see whats going on.

To adress the semi-colon problem, I can only say that it is also a pain for me. I have thought of changing to a comma based solution, but then again, that would work together with the sheet we have at work. There is no need for you to use your time on converting from commas to semi-colons, that I can do myself. I will try to substitute the semi-colons with commas, whenever I post here in the future.


Report •

#8
January 15, 2015 at 07:38:15
Fair enough. Just let us know if we can be of any more help.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#9
January 15, 2015 at 08:03:44
I will and thank you for your help until now!

Report •

#10
February 5, 2015 at 01:57:57
I found a solution to what I was looking for in this thread:

Sub multiFindandReplace()
    Dim myList, myRange
    Set myList = Sheets("WhatAndReplace").Range("A1:E6")
    Set myRange = Sheets("Target").Range("E3:E30")
    
    
    For Each cel In myList.Columns(1).Cells
        myRange.Replace What:=cel.Value, Replacement:=cel.Offset(0, 1).Value, LookAt:=xlPart, MatchCase:=True
    Next cel
    
    For Each cel In myList.Columns(4).Cells
        myRange.Replace What:=cel.Value, Replacement:=cel.Offset(0, 1).Value, LookAt:=xlPart, MatchCase:=True
    Next cel
    
End Sub

It can be altered to have as many lists as are needed. In this example I have two seperate lists in the WhatAndReplace sheet. One WHAT list in column A with the REPLACEMENT parts in column B, and another list of WHATs in column D and REPLACEMENT parts in column E.

Now I can define in those lists what items to search for, like say AA and AB gets replaced first by $AA$ and $AB$ and then afterwards A and B's (+A, ;A, (A or something similar is in the WHAT list) gets replaced by $A$ and $B$ with the "+", the ";" or the "(" in front of it.

This is going to save me a lot of time and I hope it can help others.


Report •

Ask Question