+ Reply to Thread
Results 1 to 10 of 10

Macro to copy cell contents then used Find & Replace to put text elswhere.

  1. #1
    Registered User
    Join Date
    09-10-2012
    Location
    New Zealand
    MS-Off Ver
    Excel 2007
    Posts
    6

    Macro to copy cell contents then used Find & Replace to put text elswhere.

    Hi,
    I am wanting to create a macro to do something fairly simple (well I think it is) but struggling with how.

    I have a variable that will always appear in the same cell "A9" and I want to use the contents of that cell to replace text elsewhere in the document (of which location is not always the same).

    Any help much appreciated.
    Thanks
    Aimee

  2. #2
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Macro to copy cell contents then used Find & Replace to put text elswhere.

    help us, be more precise, propose an example
    If solved remember to mark Thread as solved

  3. #3
    Registered User
    Join Date
    09-10-2012
    Location
    New Zealand
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Macro to copy cell contents then used Find & Replace to put text elswhere.

    Ok - will try to explain..
    What I am doing is I have a quote for products that is generated from our database to an excel spreadsheet. Using macros I want to be able to click a button and convert it to an "order confirmation" for our customer to check and sign. I have all the other macros sorted to change various text and add fields but can't figure this out:

    At the bottom of the document is a line that says "signed for My Company" the location of this line varies as it depends on the length of quote as to what cell it ends up in.
    I want to take the text from cell A9 (Customer Name) and replace "My Company" so it reads "signed for Customer Name".

    "Customer name" is a variable - however location (cell A9) will always remain the same.
    "Signed for My Company" text will always be the same - however location will vary.


    Hope this helps!

  4. #4
    Registered User
    Join Date
    09-10-2012
    Location
    New Zealand
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Macro to copy cell contents then used Find & Replace to put text elswhere.

    Thinking all I need is something as simple as this:

    Sub ReplaceText()
    ActiveSheet.Cells.Find("signed for my company").Replace What:="my company", Replacement:= contents of cell A9
    End Sub

    Only problem is with my very limited (basically nill) VBA experience I can't get the reference to cell A9 correct.
    Can someone help me?
    Thanks!!

  5. #5
    Forum Guru macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,703

    Re: Macro to copy cell contents then used Find & Replace to put text elswhere.

    So, since you apparently know where you're going to insert "signed for My Company", why don't you just insert a formula (="signed for "&$A$9) there instead?
    Cheers,
    Paul Edstein
    [Fmr MS MVP - Word]

  6. #6
    Registered User
    Join Date
    09-10-2012
    Location
    New Zealand
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Macro to copy cell contents then used Find & Replace to put text elswhere.

    Sorry I'm not quite sure what you are saying.
    The location of "Signed for My Company" is variable so need to use a find and replace function. I just can't figure out how to take the contents of cell A9 and use that to replace the text.
    My VBA skills are nil so been creating macros using 'record macro'.
    I would just record a Macro using the Find and Replace in Excel but just can't figure out how to reference contents of cell A9. I end up with "Signed for A9" or "Signed for (A9)" etc.
    So trying to figure out how to do it VBA but still can't figure out how to call up the contents of A9.

    Cheers
    Aimee

  7. #7
    Forum Guru macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,703

    Re: Macro to copy cell contents then used Find & Replace to put text elswhere.

    Presumably, when you create the "order confirmation", there is a process that currently adds "Signed for My Company" to a particular cell. All I'm suggesting is that, instead of adding "Signed for My Company" as text, you add the formula '="signed for "&$A$9' (without the single quotes). That way, there's no Find/Replace that needs doing.
    Last edited by macropod; 09-12-2012 at 12:54 AM.

  8. #8
    Registered User
    Join Date
    09-10-2012
    Location
    New Zealand
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Macro to copy cell contents then used Find & Replace to put text elswhere.

    No I have a copy of an original excel quote generated by our database which has "Signed for My Company" text already at the bottom (because the length of the quote always changes the location is variable) .
    I am wanting to take this quote and turn it into an order confirmation which means inserting the customer name in place of my company name. The customer name is always in cell A9.

  9. #9
    Forum Guru macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,703

    Re: Macro to copy cell contents then used Find & Replace to put text elswhere.

    In that case, it should still be a relatively simple matter (and more reliable) to have you database output the formula '="signed for "&$A$9'.

    Nevertheless, if you're wedded to the macro, try:
    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    09-10-2012
    Location
    New Zealand
    MS-Off Ver
    Excel 2007
    Posts
    6

    Thumbs up Re: Macro to copy cell contents then used Find & Replace to put text elswhere.

    Thanks macropd this works perfectly!!!! I was sure it was something simple I just needed to try and explain it to someone else!
    I have no control of the Pronto (not Excel) database the original quote was coming out of I just had the end result which was a quote in Excel format. So my only option was use macros to change it to the order confirmation format I want.

    Cheers
    Aimee

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1