+ Reply to Thread
Results 1 to 8 of 8

making macro results permanent

  1. #1
    Registered User
    Join Date
    11-02-2009
    Location
    spain
    MS-Off Ver
    Excel 2007
    Posts
    21

    making macro results permanent

    Is there some way to render the macro/formula results in a worksheet/cell permanent. For example if you using macros to extrapolate resulting information like extracting first and last name from one cell into two cells, then is there a way to freeze the resulting data so the formula is no longer there.

    Problem is arrising when I send the ws to someone else and they open the ws on their machine and the data gets distorted i.e the last name column is a list of &'s.

    Ideally would like to leave row and column data resulting from macro operations in-tact and then remove all formulas or turn them off or something so that all is left is the data with no formulas.

    Is this far fetched?

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: making macro results permanent

    Odd that they should get different results - is it not a char based issue - ie certain characters not displaying in their locale ?

    In general terms, yes you can write results over formulae, eg

    Please Login or Register  to view this content.
    that would take whatever was in A1:B10 at the time as a value and write it into the cell literally obliterating whatever formulae were there previously.

    Does that help ?

  3. #3
    Registered User
    Join Date
    11-23-2009
    Location
    Porto, Portugal
    MS-Off Ver
    Excel 2007
    Posts
    32

    Re: making macro results permanent

    I don't know if this is useful to your project but you can put the result of a formula in a cell rather than the formula itself:

    This will put the formula
    Please Login or Register  to view this content.
    This is the same thing but will output the value only
    Please Login or Register  to view this content.

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: making macro results permanent

    I'd add to that you don't always need to use Evaluate, eg

    Please Login or Register  to view this content.
    would do the same thing also ... calculating in memory certainly is another option.

    (on an aside you could omit the WorksheetFunction bit if desired but generally it's believed to be slightly quicker than without - a few functions operate differently without the WorksheetFunction qualifier than with - eg Match)

  5. #5
    Registered User
    Join Date
    11-02-2009
    Location
    spain
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: making macro results permanent

    Thanks man, not sure how to deploy this.
    objective is to send one worsheet which has 7 columns of info over 1000 rows deep with all resulting data fixed and permanent. see what mean.

    Some info for macro calculations rely on another ws data in same book.

    Would what your telling me work for this application?

  6. #6
    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: making macro results permanent

    Hello Brownstuff,

    I would first create a copy of the worksheet.

    1) Under Edit select "Move or Copy Sheet.."
    2) Check the box "Create Copy", and click "OK"
    3) Select all the cells on the original sheet by clicking the empty box next to column "A"
    4) Copy them using Ctrl+C
    5) Select the copied sheet and under "Edit" click "Paste Special..."
    6) Click the option "Values" and click "OK"

    This preserves the formatting and values from the calculations. If you want, this can be automated using VBA.

  7. #7
    Registered User
    Join Date
    11-02-2009
    Location
    spain
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: making macro results permanent

    thanks that did the trick.
    many thanks

  8. #8
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,264

    Re: making macro results permanent

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save

+ 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