+ Reply to Thread
Results 1 to 54 of 54

What's in Your Personal.xls?

  1. #1
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    List Cell Text in Binary

    After using =Char(Mid(A1, 15, 1)), =Char(Mid(A1, 16, 1)), =Char(Mid(A1, 17, 1)), ... a few hundred times in years past, I made a form and wrote some code to list the contents of a cell in binary. It's been handy.

    I recently added support for Unicode, so it may not be fully cooked (or maybe reheated), and double-width Unicode characters are problematic.

    In addition to adding the attached form to the workbook, this needs to go in a code module.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by shg; 06-15-2009 at 11:16 PM.
    Entia non sunt multiplicanda sine necessitate

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Literal Array Functions

    I use these functions in lieu of the volatile constructs like this:

    ROW(INDIRECT("1:" & A1))
    COLUMN(INDIRECT(A1 & ":" & A2))


    ... like this:

    RowArr(1, A1)
    ColArr(A1, A2)


    ... with the added benefit of having a step variable

    RowArr(1, 10, 2)

    ... and you can have negative numbers

    RowArr(10, -5, -3)

    Here 'tis.
    Attached Files Attached Files
    Last edited by shg; 06-16-2009 at 11:26 AM.

  3. #3
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    What's in Your Personal.xls?

    Just thought I'd share ...

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: What's in Your Personal.xls?

    Ever wonder about how binary (IEEE-754) floating point numbers (the source of all those mysterious precision problems) are actually represented? The attachment has a family of functions for converting between floating point numbers and hex strings. I used a couple of these in responding to http://www.excelforum.com/excel-prog...h-queries.html.

    Most work in either VBA or as worksheet functions. Here's the list:

    Please Login or Register  to view this content.
    Attached Images Attached Images
    Attached Files Attached Files

  5. #5
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: What's in Your Personal.xls?

    I’ll play even though by comparison, I fear I’m a bit lowbrow.

    I have a number of macros which are specific to my job – organising, running checks on and analysing data.

    I have a macro listing names of files in a folder, and one which extracts data from files in a folder. I have a couple of template-style macros which contain little bits of code which I tend to use more often than most (e.g. Find).

    I also have a number of very short macros which I’ve assigned to buttons on a toolbar. They’re so simple but they save so much time. For example, vertically centring text, removing colours, turning off AutoFilter, centring across selection, filling selected cells with random numbers.

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: What's in Your Personal.xls?

    My thought was that we could use this thread as a repository of sorts, and vector people here when one of the routines might solve a problem.

    I have a macro listing names of files in a folder, and one which extracts data from files in a folder. I have a couple of template-style macros which contain little bits of code which I tend to use more often than most (e.g. Find).

    I also have a number of very short macros which I’ve assigned to buttons on a toolbar. They’re so simple but they save so much time. For example, vertically centring text, removing colours, turning off AutoFilter, centring across selection, filling selected cells with random numbers.
    Those sound great! How making a separate post for each?

    So -- post 'em up, Stephen!

  7. #7
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: What's in Your Personal.xls?

    Mostly have a lot of little utility macros with shortcut keys if I use them often.
    Here's one where I select all the cells in my workbook that I want to have a specific # of digits
    Please Login or Register  to view this content.
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  8. #8
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: What's in Your Personal.xls?

    And here's one that Inserts a worksheet and lists all the formulas off the active sheet and what cells they are in
    Please Login or Register  to view this content.

  9. #9
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: What's in Your Personal.xls?

    I use this one a lot before protecting my workbook. It selects all unlocked cells.
    Please Login or Register  to view this content.

  10. #10
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: What's in Your Personal.xls?

    Mine are mostly specific to my workplace, like changing Csv files to a nicely Formatted BOM form.. or actually consolidating quantities in CSV files to BOM's, etc..

    But here is a simple one that I am sure people have already access to, I use to delete blank rows...

    Please Login or Register  to view this content.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  11. #11
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: What's in Your Personal.xls?

    I just wanted to say I don't use a Personal.xls... adding no value to the thread per se just thought I should point out how odd I am... and wondered if anyone else out there like me... I do have a libary of exported modules (.bas) which I import to files as and when but that's about it (along with some old .xla's)...

    Interesting thread... cheers shg.

  12. #12
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: What's in Your Personal.xls?

    You're in good company -- Andy Pope doesn't use one.

  13. #13
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: What's in Your Personal.xls?

    Maybe it's just because we're both "Essex Boys" ...
    (I might live in Suffolk in the UK but I'm a born & bred Essex Boy... note to non-Brits - that's not a good thing in terms of sterotypes...)

  14. #14
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: What's in Your Personal.xls?

    I had a friend in Essex who was continually at pains to point out that he lived in the posh bit (which was virtually Suffolk).

    I don't claim authorship of any of these except the really short ones, some of which could probably be done with keyboard shortcuts but originate from when I first discovered VBA and wanted to do everything with macros.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.

  15. #15
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: What's in Your Personal.xls?

    I had a friend in Essex who was continually at pains to point out that he lived in the posh bit (which was virtually Suffolk).
    Are we friends but don't know we're friends ??? Me too...
    (If he was indeed a he, and he continuously referred to his hometown as the oldest recorded town & former Roman Capital of Britain (Camulodunum) and former home to Damon Albarn of Blur then there's a good chance we know each other...)

    Promise I won't darken the thread further unless I have something worthy to add. Sorry.

  16. #16
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,440

    Re: What's in Your Personal.xls?

    A couple of functions that I find useful:

    Range
    Please Login or Register  to view this content.
    Multi cell concatenate
    Please Login or Register  to view this content.
    interrogate cell colour
    Please Login or Register  to view this content.
    interrogate cell formula
    Please Login or Register  to view this content.
    Upper and Lower control limits:
    Please Login or Register  to view this content.
    Note that these are just to "get by" and don't error trap (and probably aren't particularly well written)

    Dave

  17. #17
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: What's in Your Personal.xls?

    DonkeyOte: oh my God I can't believe it, you're ... well it is uncanny except that my friend would have had difficulty spelling Excel never mind penetrating the mysteries of VBA. So, Damon Albarn left Essex did he once he made some money...who'd have thought it.

    Apologies to shg for derailing his thread.

    Thanks sweep - what do you use UCL and LCL for?

  18. #18
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,440

    Re: What's in Your Personal.xls?

    Thanks sweep - what do you use UCL and LCL for?
    I used to use it all the time for control limits on XBar & R charts. However, no I have Minitab, it's not used so often...

  19. #19
    Forum Contributor
    Join Date
    04-03-2007
    Location
    Auckland, New Zealand
    MS-Off Ver
    2007
    Posts
    137

    Re: What's in Your Personal.xls?

    As I regulary use workbooks with many sheets, and often hide a number of sheets from time to time I find the below useful.


    Please Login or Register  to view this content.

  20. #20
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    Re: What's in Your Personal.xls?

    I store the macros I've pinned to keyboard shortcuts in one module. I start them all ok (OnKey) so they're easy to discern/ignore in larger lists:
    Assigning the macros to various application.onkey strings is done in my personal.xls workbook open event.

    Please Login or Register  to view this content.
    I find the last of these very useful. Whilst working on a sheet, I can quickly colour cells needing special attention for any reason (I have pinned it to ctrl+g as I use Go To very rarely). The cycle is very powerful, once for green, twice for purple, etc. is super quick. Any "mixed" group is blanked first, then coloured (which can be quite helpful depending on which colour-happy mentalist sent you the sheet you're working on).

    CC

  21. #21
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: What's in Your Personal.xls?

    Always at hand for working with PowerPoint, Word and large Data
    Please Login or Register  to view this content.
    If the solution helped please donate to RSPCA

    Site worth visiting: Rabbitohs

  22. #22
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: List Cell Text in Binary

    I utilize Named Ranges in many of my Excel apps. Occasionally, some bozo..um...I mean: an esteemed colleague will completely destroy key named ranges by deleting critical cells.

    I've gotten into the habit of using Insert.Name.Paste...Paste List
    to archive the definitions.

    My PERSONAL.XLS has this macro to convert that listing back into Named Ranges:

    Please Login or Register  to view this content.
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  23. #23
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: What's in Your Personal.xls?

    ron i thought i could use that code to insert multiple tables but i keep getting error
    any ideas?
    Attached Files Attached Files
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  24. #24
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: What's in Your Personal.xls?

    Quote Originally Posted by martindwilson View Post
    ron i thought i could use that code to insert multiple tables but i keep getting error
    any ideas?
    Using your posted workbook I didn't have any trouble.
    On Sheet2, where Col_A contains the "Names" and Col_B contains the "Refers To" expression.
    Here's what I did:

    1) Select A1:B101
    2) Tools.Macro.Macros....CreateRangesFromList....Click: Run

    All of the range names were created.

    Did you select the 2-col list before running the macro?
    Last edited by Ron Coderre; 07-18-2009 at 09:31 PM. Reason: Corrected mindless typos :\

  25. #25
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    Re: What's in Your Personal.xls?

    You should make the two column list a named range so you wouldn't need to bother with that first bit... oh wait...

  26. #26
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: What's in Your Personal.xls?

    Thanks Ron
    another keeper

  27. #27
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: What's in Your Personal.xls?

    cheers ron, guess who selected whole range a:b instead of a1:b101, oops

  28. #28
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    Re: What's in Your Personal.xls?

    I wrote this recently, trying to decipher a really mixed up worksheet, with multiple different formulae at different points in columns & rows.
    The comments show exactly how it works, but in summary, it returns true if the formulae of the two specified cells are equivalent (based on relative referencing, of course).

    Please Login or Register  to view this content.

  29. #29
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: What's in Your Personal.xls?

    I love my Personal.xls, have all sorts of useful stuff in it that helps me with my job.

    From the top...

    - a bunch of subs that summarise/format SAP reports in different ways depending on what I want to do with them
    - a batch pdf creator
    - functions for counting/summing by font and colour. These aren't really for me but many people I work with have a habit of colouring cells/text and then realise they can't add the colours up.
    - a toggle between manual and automatic calculation
    - one that summarises consecutively dated records that SAP throws out
    - one that compares worksheets and reports the differences
    - a password breaker (don't tell)
    - delete all named ranges
    - file lister
    - Chip Pearson's ImportBigTextFiles

    I never realised there was quite so much in here...loads of other little functions and things that I've written or stolen over time really.

    Dom
    "May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."

    Use code tags when posting your VBA code: [code] Your code here [/code]

    Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.

  30. #30
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: What's in Your Personal.xls?

    Just had time to read through this one. I don't have a Personal.xls, I prefer the mobility of an addin.

    I also keep a large database of code snippets.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  31. #31
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: What's in Your Personal.xls?

    I agree add-ins are probably better and I use those when I've written something to share with others but if it's just for me I use my personal.xls and keep a copy in my Google Docs so I can download it wherever I want.

    Dom

  32. #32
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    Re: What's in Your Personal.xls?

    - a toggle between manual and automatic calculation
    I have this, it's called 'dumbass mode button', when I click it my statusbar shows 'warning, dumbas mode enabled'
    CC


    If you feel really indebted please consider a donation to charity. My preferred charity is ActionAid but there are plenty of worthy alternatives.

  33. #33
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    Re: What's in Your Personal.xls?

    Dom, I'd really appreciate a look at your compare spreadsheets code, if you don't mind?

  34. #34
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: What's in Your Personal.xls?

    Charlie, I liked your Compare function. It will definitely come in handy when validating huge spreadsheets with complex formulas. Thanks!

  35. #35
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: What's in Your Personal.xls?

    You're welcome to it although it's probably not quite what you're thinking of from my post. It's just some code that I use to compare the output of SAP reports that we've run pre and post applying patches to see if they've had any effect on the output of the reports.

    It highlights the differences and sticks hyperlinks to the first 255 in the first couple of rows of the second worksheet (I work on the fact that if there's more than 255 differences we've got real problems )

    I wrote this quite a long time ago so it could be greatly improved.

    Please Login or Register  to view this content.

    Dom
    Last edited by Domski; 08-05-2010 at 10:45 AM.

  36. #36
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    Re: What's in Your Personal.xls?

    Normally I can't be bothered with tidying up functional code - even it could benefit from it, but this:
    Please Login or Register  to view this content.
    Made me write this, general tool for picking either any worksheet in the active workbook, or any open workbook - something I've written individual versions of too many times... not any more!

    Please Login or Register  to view this content.
    Supported (obviously?) by a very simple userform with one label, one combobox and two buttons, one (cancel) clears the combobox entry before hiding, (ok) just hides the userform.

    If Picker returns "" then consider it a cancel in either case...

    Not sure it was worth the endeavour, but I needed a break, work's killing me today!

  37. #37
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: What's in Your Personal.xls?

    FWIW I put together an example of this working for this post...

    http://www.excelforum.com/excel-prog...o-compare.html

    Dom

  38. #38
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: What's in Your Personal.xls?

    I couldn't resist...

    Please Login or Register  to view this content.

  39. #39
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: What's in Your Personal.xls?

    Here's another alternative
    Attached Files Attached Files

  40. #40
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    Re: What's in Your Personal.xls?

    I couldn't resist...
    :D Know the feeling

    I like the use of choose (all of them), but I'm not a fan of iif. (spit).

    Why add the elements to a string then split the string into the box rather than add the items straight into the box? (I don't mean to make any judgement either way, just curious).

  41. #41
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: What's in Your Personal.xls?

    Why add the elements to a string then split the string into the box rather than add the items straight into the box? (I don't mean to make any judgement either way, just curious).
    with .List you don't need 'clear'
    .additem is noticebly slow
    .list is designed for assigning multiple items to a listbox/combobox at a time

    you could also use an array, but I like the simplicity of split (no need to dim, redim, redimpreserve etc.)

  42. #42
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: What's in Your Personal.xls?

    Here's one that I have passed on to many people.

    Please Login or Register  to view this content.

  43. #43
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: What's in Your Personal.xls?

    I went back to this thread because I've lost some personal macro books along the way but it appears the first part of this thread is beyond viewing. Is there something in setup where I can view older threads?

  44. #44
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: What's in Your Personal.xls?

    I'm a mouse person so I have a bunch of macros that I attached to buttons to do things so that I don't have to use the keyboard. A few of them are:

    Escape (kills the marching ants!) Yeah, I know, all I have to do is press the Escape key but like I said, I'd rather use the mouse!

    Goto cell A1

    Insert random dates in the selected cells

    Insert random numbers in the selected cells

    Remove conditional formats, data validation, convert formulas to constants

    Charles Williams' code for doing calculation timings

    David McRitchie's TrimAll

    One of my favorites is code from Dave Peterson to show the full file path in the title bar. This one goes in the ThisWorkbook module.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  45. #45
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: What's in Your Personal.xls?

    Quote Originally Posted by ChemistB View Post
    I went back to this thread because I've lost some personal macro books along the way but it appears the first part of this thread is beyond viewing. Is there something in setup where I can view older threads?
    I had no problem, CB. Does http://www.excelforum.com/tips-and-t...nal-xls-2.html work for you?

    If not, try increasing the number of posts per page in your preferences.

  46. #46
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: What's in Your Personal.xls?

    Is the first one "List Cell Text in Binary"?

  47. #47
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: What's in Your Personal.xls?

    Yup .

  48. #48
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: What's in Your Personal.xls?

    This thread title reminds me of the Capital One commercials...whats in your wallet? lol
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  49. #49
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: What's in Your Personal.xls?

    How can I forget to give more props to Marcelo Branco for his great work in coming up with the "make a table" macro? I love being able to do this with just 4 clicks:

    Data Range
    A
    B
    1
    Date
    Value
    2
    2/26/2001
    49
    3
    4/26/2001
    97
    4
    8/19/2004
    57
    5
    3/13/2006
    83
    6
    10/11/2008
    57
    7
    1/1/2010
    80
    8
    3/25/2012
    31
    9
    4/24/2014
    39
    10
    4/29/2014
    97
    11
    7/30/2014
    17


    2 clicks to fill the data and 2 clicks to generate the table.

  50. #50
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: What's in Your Personal.xls?

    Will you share the code with us, Tony?

  51. #51
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: What's in Your Personal.xls?


  52. #52
    Forum Contributor Gregor y's Avatar
    Join Date
    10-24-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2010 32-Bit
    Posts
    274

    Re: What's in Your Personal.xls?

    For when copy'n'paste from Access gives you cells that have numbers with a cell format of general yet still come across as text. (You can see what I mean by: 1. format empty cell as Text, 2. enter value 1 into it, 3. format it back as General. Now the 1 stays 'Text' until you edit the contents of the cell... and 'touching' a bunch of these cells manually is no fun.)

    Please Login or Register  to view this content.
    to convert copy/paste values from old style pivot table into an actual data table
    Please Login or Register  to view this content.
    a sub to clean up pasted text and a function to check it via =A1<>ScrubText(A1)
    Please Login or Register  to view this content.
    and one to see what text got Scrubbed
    Please Login or Register  to view this content.
    Last edited by Gregor y; 07-25-2014 at 09:10 PM. Reason: forgot one

  53. #53
    Forum Contributor Gregor y's Avatar
    Join Date
    10-24-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2010 32-Bit
    Posts
    274

    Re: What's in Your Personal.xls?

    I think this one at least in part is not mine, but I can't remember where it came from. So if I'm stepping on your code toes, I'm sorry but thank you because its been very helpful while checking macro run times.

    Please Login or Register  to view this content.

  54. #54
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: What's in Your Personal.xls?

    It seems that I almost never used any of the "built in" number formats in the format cells dialog. At one point, I just got tired of navigating to the format cells dialog and finding the "custom" entry field (and since 2007 hitting the "add custom" button). I created a very short, simple routine attached to a toolbar button that pulls up a simple text input box where I can input my number format code and it will apply that code to the selection.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

+ 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