Posts by AndrewJ

    Re: Locking Columns

    Couldn't you just protect the sheet, with only the 'format columns' and 'insert column' not checked? Then you wouldnt have to name a bunch of ranges.

    Re: Dynamic Importing From Spreadsheet To HTML/php/asp..

    Are you positive you want to go the route with external documents?

    Seems like you could just hide the columns, password protect the worksheet, or password protect the vba, and then distribute it. Or are you not wanting that info out there at ALL?

    Re: Activesheet Gets Uncapitalized


    Aside from making sure you arent declaring a public variable somewhere (search the entire project for 'activesheet') I really cant think of anything besides messing with your options under 'tools' in the VBE.

    Sorry, man :/

    Re: Activesheet Gets Uncapitalized

    I would look for a place earlier in your macro where you referred to it as 'activesheet' and see if somehow you set it to a variable. When you define a variable in your code, every time you type it in later it will auto-format to the way you first typed it.


    Dim ThisVariable

    now every time you type the word 'ThisVariable' in your macro it will capitalize the T and V and leave the rest lowercase, even if you type something like 'tHIsvarIABLe'

    activesheet shouldn't be a variable tho... but this is the only thing I can think of that might explain it.

    Re: Large Window Input Box

    Just use a userform.

    Insert a userform, add a textbox, a button, and then add some code to the click event of the button to save the textbox data to wherever you want.

    Re: Complete Statusbar Progress Meter

    New Version:

    - Apparently sped up the macro by ~ 200 times.... lol. It now does an empty loop of 1 million records in just over one second on my machine. Who knew changing only a couple variants to their proper variable type made that much of a difference? I was aware they were slower, but WOW!
    - Added a timer for the time between statusbar updates, now it will update at 1 second, .5 seconds, 3 seconds, or whatever is set.
    - Added a few misc. format options for displaying.

    If a mod could maybe merge this with the first post, or edit the first post to point to this one I would very much appreciate it!

    Re: Pair Matching/linking

    Well, this is gonna be ugly, but I think itll probably work.....

    I tested it and it outputs all the correct relations (in columns E, G, I, etc..) although you'll need to clean it up as there are a bunch of duplicates.

    Havnt tested it thoroughly but I think it should work with more than 2 columns and as many rows as you want. The only hangup I *think* might be if you have something like this:

    1 100 200
    2 100 300
    3 400 500
    4 300 900
    5 600 700
    6 700 800

    It may not know to begin with the 400 500 to start the second relationship, because the macro would set the lastusedrow to row 4 (300 900) and would start at row 5 for the second relationship. If this happens and it messes up what youre trying to do Ill look at it and see if I can fix it. Quick fix would be to store row numbers in a separate array, loop through it until you find the lowest number not used, and then set lastusedrow to that.

    Re: Open Workbook Based On Cell Value

    Quote from ExcelDork

    Of course the other people who use it will not appreciate it or will complain that it doesn't do more, but it now does what it was meant to do.

    lol, always the problem huh?

    you're welcome

    Re: Open Workbook Based On Cell Value

    Oh btw, if youre doing a big copy/paste, two things:


    'range' statements can replace the 'cell' statements if you want more than one cell. I am in the habit of using the 'cell' statment since I run tons of loops on lists and such for work, but there isnt really any difference aside from 'range' can be multiple cells, and you can refer to variables directly within the 'cell' statement.

    second thing:

    Application.CutCopyMode = False

    will clear the clipboard if youre doing a big copy and paste. This will keep your spreadsheets working fast and wont bog down windows. Add the cutcopymode line to the very end of the sub.

    Re: Open Workbook Based On Cell Value

    You wont actually need to activate the workbook if you are only copy/pasting data

    Dim strWB as String
    strWB = varCellvalue & ".xls"
    Workbooks(strWB).Sheets("Sheet1").Cells(1, 1).Copy Destination:=ThisWorkbook.Sheets("Sheet1").Cells(2, 2)

    This will pull the value of A1 from the newly opened workbook into cell B2 of the one you are running your code from. The 'thisworkbook' command is used to refer the specific workbook that is running the code, so if you do need to activate the other workbook for whatever reason, 'thisworkbook' will refer to the original one regardless of which is active.

    Re: Transposing 365 Days Of 24 Hour Data Into One Column

    Well, I think VBA is the way to go to be honest with you. It seems to me there's a function that can do this but I cant recall what its called....

    anyway, make a backup copy of the workbook before you do any of this.

    Then, hit alt-f11 and insert a new module. Put this in the module and run it

    (this assumes your data is IN sheet1, and will be moved TO sheet2)

    edit - didnt see you wanted it in one column, its fixed now