Results 1 to 4 of 4

Thread: replace part of text held in a merged cell

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Jun 2012
    Posts
    90

    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

  2. #2

    Thread Starter
    Lively Member
    Join Date
    Jun 2012
    Posts
    90

    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?

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Jun 2012
    Posts
    90

    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]

  4. #4
    Addicted Member 3com's Avatar
    Join Date
    Jul 2013
    Location
    Spain
    Posts
    253

    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
  •  



Click Here to Expand Forum to Full Width