-
Nov 27th, 2013, 06:05 AM
#1
Thread Starter
Lively Member
replace part of text held in a merged cell
Hi
I have a merged cell and within that I have some text. So the text is something like;
"the company name is {Company Name} and the contact is"
I'm using the following code to replace part of the text but nothing is updated via the code. When I manual run the find/replace in Excel then it works perfectly. I guess I'm doing something wrong but not sure what.
Code:
.Replace What:="{Company Name}", Replacement:="New Company 1", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
If the text is found in a single cell then the code works fine.
Thanks
-
Nov 27th, 2013, 06:58 AM
#2
Thread Starter
Lively Member
Re: replace part of text held in a merged cell
ah. solved it. Originally I was using a range set with just the columns (A:Z) and once I changed that to include row numbers then it works! No clue why.
So I used this;
With shtDest.Range("A1:Z1000")
Instead of this;
With shtDest.Range("A:Z")
Any way I can get it to work without using the row numbers?
-
Nov 27th, 2013, 07:01 AM
#3
Thread Starter
Lively Member
Re: replace part of text held in a merged cell
I'm on a roll ... it works exactly as I want it to now. I used the following;
[CODEON]With shtDest.range.usedrange[CODEOFF]
-
Nov 27th, 2013, 11:49 AM
#4
Addicted Member
Re: replace part of text held in a merged cell
Or I can also propose a new scenario.
Type the company name in the textbox1 and the contact in the Textbox2.
Pass the name of the company to the company variable and the contact to the contact variable.
Then assign these variables to the respective procedures. example:
Code:
Dim NameCO,contact as string
NameCO=Trim(Textbox1.text)
contact=Trim(Textbox2.text)
Textbox3.text= "the company name is" & NameCO & " and the contact is " & contact
HTH
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|