Find text and append more text

July 13, 2011 at 10:06:04
Specs: Windows XP
I am using Micorsoft Excel 2003. I have a string of text as a placeholder and I need to replace that with a couple sentences. The cells containing this text also have other text within them, creating the issue with too many characters. I am working with a macro. I have tried using the find/replace function in Excel, but I am exceeding the function's character limits.

I tried breaking up the find/replace function from one action to 3, but it still does not work for all of the cells.

Is there a way to search for a specific string of text, then append more text to that?

Thanks!


See More: Find text and append more text

Report •


#1
July 13, 2011 at 11:37:57
Please post the code you have tried...maybe it just needs a little modification.

Also post an example of the what you are trying to append or at least a little more information so that we can understand what you mean by "I am exceeding the function's character limits."

Before you post any code or data, please click on the following line and read the instructions on how to post data and code in this forum.

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


Report •

#2
July 13, 2011 at 12:59:59
My work is on an offline computer, so I just typed up an example here. I am using Excel 2003, so I'm not sure if the limits are different for other versions. Here is some more background: I ran into the isse when running a macro someone else had written, which was recorded using the find and replace feature in Excel. Since it was written, I believe there are new cases of cells with more than 930 characters in them. The string I am looking for has 57 characters, and the replacement for that has 224 characters. When running the macro there were not error messages, but when I checked the cells I realized that they did not all have the text replaced. That's when I ran the find and replace manually, which returned the "formula is too long" message. Internet searching tells me that I have too many characters for the find/replace function (1024 characters).
I then tried to break up the VBA code into pieces to relieve the find/replace function, and it worked for more of the cells but still does not replace the text in all instances.

Here is an example of the code I currently have:

Sub replaceNote()

Range("A1").Select
Application.CutCopyMode = False

Dim a As String
Dim b As String
Dim c As String
Dim d As String
Dim e As String
Dim f As String
Dim g As String

a = "_This_is_what_I_want_to_replace."
b = " Will this new "
c = "code work?"

d = "Here is my replacement string.  I cannot use the "
e = "actual file because it's on an offline computer.  In the actual work I am doing there are 224 characters "
f = "in the replacement text.  Let's see if I can get the exact amout here."

Cells.Replace what:= _
a, replacement:= _
d, Lookat:=xlPart, Searchorder:=xlByRows, MatchCase:=flase, searchformat:=False, ReplaceFormat:=False

Cells.Replace what:= _
b, replacement:= _
e, Lookat:=xlPart, Searchorder:=xlByRows, MatchCase:=flase, searchformat:=False, ReplaceFormat:=False

Cells.Replace what:= _
c, replacement:= _
f, Lookat:=xlPart, Searchorder:=xlByRows, MatchCase:=flase, searchformat:=False, ReplaceFormat:=False


End Sub


Report •

#3
July 13, 2011 at 13:02:24
I forgot to add, the file has 194 rows of data. Could this also contribute to the problem?

Report •

Related Solutions


Ask Question