Posts by gijsmo

    Re: Increase Code Efficiency

    As you have only supplied a snippet of code and no description of the project, the "condensed" code I gave you made some assumptions eg, that the DeleteButtons subroutine exists somewhere in your project. I am not sure what this does as you did not include the code in your example.

    Also, I had to presume that the labels you want to make visible were invisible to begin with (ie, at design time).

    The Private variable has to be placed at the top of the UserForm module.

    To show you how this all hangs together, a sample "dummy" file is attached with the code in it. If you give it the correct password, the corresponding label will display. You will have to run the UserForm1 manually, I have not added any code to launch this automatically.

    Re: Increase Code Efficiency

    Try this instead...

    Re: Multiple filter, 1 Coulmn

    When you want to do either Red or Blue, you will need something like:

    ActiveSheet.Range("A2").AutoFilter Field:=3, Criteria1:="Red", Operator:=xlOr, Criteria2:="Blue"

    Re: Indirect reference to sheet names in macros

    It's always good practice (and a memory saver for large projects) to properly define your data types.

    If you change your declaration to:
    Dim data As String

    I think you will solve the problem. Without declaring the data type, Excel will interpret 2011 as a number and your code is telling it to access sheet number 2011 which is unlikely to exist.

    Re: Find and remove specific numbers

    Give this a try....

    Re: Custom Number Function

    1. Press Ctrl+1 to open the Format Cells dialog box.
    2. Select the Number tab, and from Category, select Custom.
    3. In the Type box, enter the following Custom Formatting syntax:

    Re: Indirect reference to sheet names in macros

    Take a look at using named ranges. The help on referring to named ranges in Excel VBA is not too bad.

    Lets assume you have a named range called REFYEAR (this would be your cell HOME!A17). You can then do something like:

    listname = Range("REFYEAR").Value
    or more pedantically
    listname = Range("MyBook.xls!REFYEAR")
    or even
    (assuming the tab with the named range is called HOME)

    and then follow that up if you want to select the sheet:
    (notice no quotes around the variable name)

    Re: How to display an answer with 3 possible outcomes

    Lets assume you meant "x", "y" and "z" in your example, otherwise you may as well just set the cell value to "x" :)

    Try something like this example (this one uses cell A1 as the cell we are looking at):


    Re: Import and Exporting from specific ranges

    Yes, a lot of time goes into this sort of coding, but it helps when you have other projects to draw "common tools" (and inspiration) from. Helping out in this forum builds that toolkit up.

    Again, I need to emphasise that a spreadsheet example paints a thousand words! Without an example, it is often guesswork as to the data we are working with. If I have interpreted what you wrote correctly then cells A98:A100 are merged and there are no other (to be avoided at all costs!) merged cells.

    You can use a modified approach to deal with this that still uses the PasteSpecial method. It just involves dealing with the merged cells separately. Only the ExportTrades and ImportTrades subs have been modified, the rest remains the same.

    Re: Import and Exporting from specific ranges

    OK, I think I've pieced together what you are trying to do - a copy of the worksheet with dummy data (or sensitive data removed) is always helpful in these situations. My interpretation without a sample of your data has lead to the following code that should do what I think you are trying to do:

    There's a number of assumptions made and I notice that some of the ranges you are backing up have 85 rows and others have 87 rows but you seem to copy 87 rows from the backup file in all cases. My code goes strictly by the number of rows and columns you are backing up.

    I have also taken the liberty of adding some validation (I do this on all my's good practice). Of course, there is no specific validation on the file that is selected for import, it will just open the file nominated and copy the data straight in.

    Hopefully, there's enough here for you to take it forward.

    Re: Import and Exporting from specific ranges

    There are certainly more efficient ways to perform the ImportTrades function.

    However, I am confused as to what you are trying to do in this function. When you Export, you are exporting ranges like A14:A98, E14:E98, etc into a new workbook. If I read this right, you are just saving a workbook with a single worksheet that has selective ranges pasted to it (eg, A14:A98 in the backup workbook contains the same data as the file it was backed up from). By the way, I do not think your Export will work, the Range("A14:A98...").Select and Selection.Copy does not work for me (Excel 2003) - it gives me a "That command cannot be used on multiple selections" error.

    When you import you seem to be copying from A1:A87, B1:D87, etc on the backup sheet into A14:A98, E14:E98 which does not make sense to me. I would have expected that the source range is A14:A98, E14:E98, etc on the backup file being pasted to A14:A98, E14:E98, etc to the destination (the current file).

    Re: Import and Exporting from specific ranges

    Conceptually, if the workbook is not too large (and not too many other worksheets in it), I would think about a 3 step approach:
    1. Save the whole workbook as "Portfoliobackup(today's date).xlsx" - this can be done via macro if need be.
    2. Have a macro that can allow the user to select a folder/file to restore data from. This is not too hard - eg, maybe a button on the "My Investments" sheet somewhere.
    3. Open the selected workbook as read-only (via a macro) and import the data from the relevant range - grab the format too if necessary. The macro can then close the backup workbook as well.

    Re: Update spreadsheet from another spreadsheet

    There are a number of different ways (as always) to approach this in Excel and I am not sure what is best suited in terms of overall performance. You can use vlookup or a range of other options but as the columns and rows in the 2 spreadsheets do not line up, the simplest method may be to just to use the Find function looping through the reference numbers. This can be slow with large numbers of rows and/or columns as the macro will be finding the same columns for every row that matches in spreadsheet 2. However, it is flexible as it won't really matter where the matching rows and columns are and no additional cells or sheets need be touched during the update.

    Coincidentally, this is reasonably similar to a project I completed for a colleague and I have modified that code to work with the example provided. It should also work with the actual sheets you have provided:

    • The "Reference" column is leftmost in each sheet (it does not have to be column 1, the rest of the data just has to be to the right of it. The word "Reference" must also exist in the header of that column.
    • Sheet 1 is the sheet we are interested in on both sheets.
    • Both sheets have a single header row and each column in the header row contains a description (no "blanks").
    • The "Reference" column in both sheets has no gaps (ie, there is a reference number for each row).

    Attached is a demo using your example data. Note that I shifted the columns around in spreadsheet 2 just to confirm the macro would still update the master correctly. Open the DataUpdater spreadsheet to run the demo, it contains the code. You will need to change the folder where Spreadsheet1 and Spreadsheet2 are located (double click on the cells that contain the folder/filenames and a folder selection form will display). A modified version of the code could of course be placed in the master spreadsheet but if the master is passed around to other users, you may not want code in it.

    Neither the master or the file you are updating from need to be open in Excel although it won't matter if they are. Note the master file will be saved when the update is completed.

    Re: Need to write Macro for transfer of Data

    Try this bit of code in your "Forecast" sheet:

    The code will copy the values and number formats to the "Realized" sheet if a change is made that affects the formula values in column A.
    If you actually wanted to copy the formulas to the "Realized" sheet as well (I assumed you did not), then change the middle bit of code to

    rRange.Copy .Sheets("Realized").Range("A9")
          .CutCopyMode = False

    Re: Rename Files in Folder - Change Date Format

    I am not familiar with Regular Expressions so I don't fully understand the terrific code provided by jindon...but I will be looking at this more closely.

    However, I have updated my demo with a different way to search sub-folders and also to search for pdf files (using a cell on the worksheet to specify the file type).

    This version will work only on files that are in the format mm-dd-yy.pdf
    If the date part is embedded in the filename somewhere, then the regular expression method is likely to be more useful.

    Re: Rename Files in Folder - Change Date Format

    Making some assumptions based on the information you provided:

    • original files have names in the mm-dd-yy.xls format, the renamed the files will be in the yyyy-mm-dd.xls format
    • only files in the main folder will be renamed, files in sub-folders (if any) are ignored
    • all files are assumed to be Excel files (*.xls? eg, .xls, .xlsx etc)
    • all files are assumed to be able to be renamed (eg, not read only and not open in Excel)

    The attached demo code should do what you want. It will cycle through a list of Excel files found in the source folder and rename them if the input format matches eg:
    05-06-11.xls --> 2011-05-06.xls
    12-21-10.xls --> 2010-12-21.xls

    My native short date format is dd/mm/yyyy but the code will explicitly look at the individual components of the input date to correctly perform the rename.

    Re: Missing Workbook

    You may have accidentally hidden the worksheet/s in the workbook. Open the workbook and look at the Window menu (in Excel 2003) to see if the Unhide option is available. Click on that and it will bring up a menu of worksheets you can unhide.

    Re: Non-activex Datepicker Calendar Control

    Hi padster18,

    I did a bit of work a while back in this code to "internationalise" it as much as possible. If you are using the Calendar form module from the DatePicker 8.5 demo or the DatePicker addin demo (form Version 2 Build 7) then it should format the date based on the system Short Date settings that you specify in the Control Panel (Regional/Language options).

    If you look at my DatePicker form module it uses the date formatter function (from the Calendar module) to set the date format "Calendar.FormatDate(Date)". This ensures a consistent date format as the Calendar.FormatDate function uses an internally derived format based on the system Short Date.

    One thing to note is that if the Short Date is not returned by the system call, it will try and use the Long Date format and if both fail it will default the date format to "dd/mm/yyyy"

    So, first of all I would check the Short and Long Date settings on your system and then also confirm you are formatting dates in your userform using the Calendar.FormatDate function.