+ Reply to Thread
Results 1 to 6 of 6

Macro for find and replace

  1. #1
    Registered User
    Join Date
    02-09-2010
    Location
    Canberra
    MS-Off Ver
    Excel 2007
    Posts
    4

    Macro for find and replace

    I have a worksheet from a customer which include negative numbers however the negative numbers have the negative sign at the back.

    Thesse are three columns as an example..

    Please Login or Register  to view this content.

    I wish to develop a macro to find the cells with this negative number in the worksheet. So I need to find cells with "-" sign at the end , for these cells I need to remove the "-", and then multiply the number to get into correct negative format.

    Like this..

    Please Login or Register  to view this content.

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Macro for find and replace

    Hello Leeman ,

    Welcome to the Forum!

    Have you tried selecting the column and reformatting the numbers?
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Registered User
    Join Date
    02-09-2010
    Location
    Canberra
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Macro for find and replace

    Hi, Yes I have tried this. It is not a formatting issue. The negative signs are a character inserted at the end. Its a direct out put from the customer's system. They cannot change the output to have the negative sign at the front so I have to find a way to find "-" in cells in a worksheet range and replace with blank, then if that cell did have a negative at the end, it should be multiplied by -1.

    Thanks for the Welcome


    Ive just completed a VBA Excel 2007 course, so im on a steep learning curve. Any help is appreciated and hopefuilly in time I will be able to help others...Learning through teaching

  4. #4
    Registered User
    Join Date
    02-09-2010
    Location
    Canberra
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Macro for find and replace

    I figured my approach would go something like this....

    Open workbook
    look on first sheet 'data is only on first sheet
    find any cell with "-" at the end of the string
    For all cells with that are found
    replace this "-" with "" and then multiply by that cell by -1
    do for all cells (loop required)
    Save worksheet

  5. #5
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Macro for find and replace

    Hello Leeman,

    Try this macro in your Workbook_Open event.
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    02-09-2010
    Location
    Canberra
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Macro for find and replace

    This worked great thanks. I had to change one thing as shown below because I am running this macro from one book, and running it on another. It kept updating the worksheet with the macro, not the target worksheet.

    I included this line within the loop so it updates the cell value as it goes.

    Please Login or Register  to view this content.


    Please Login or Register  to view this content.
    Thanks again for your assitance. I learnt something from you. Appreciated and SOLVED
    Last edited by Leith Ross; 06-15-2012 at 11:34 AM. Reason: Corrected Code Tags

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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