Posts by gijsmo

    Re: Lookup & open file based on partial filename

    I have had to modify the code again after a bit more testing. There was a bug or two related to the changes made when I switched to using the new FindInUsedRange function. There has also been some tidying up of the error handling.

    As I was not able to edit my post again, I have pasted the entire new module below:

    As before, best to replace ALL existing code with this.

    Re: Lookup & open file based on partial filename

    Having said in my last post:


    How you decide to break up a piece of code into a Sub or Function is sort of arbitrary but code that is repeated is a good candidate for a Sub or Function...

    I thought maybe I should take my own advice.

    Changing the code last time to be more "dynamic" meant that the Find method was repeated an awful lot of times. So I have encapsulated the Find method into it's own function which tidies up the code considerably (and hopefully makes it easier to follow). Hopefully, also, you will see that the new Sub called FindInUsedRange is generic and flexible enough to be used in other programs.

    So the latest revision of the code is here:

    Re: Non-activex Datepicker Calendar Control

    As suggested to wsautrey1000, an updated version of the DatePicker demo using named ranges is attached. I have created a named variable called POPUPTYPE which controls the behaviour of the pop-up calendar on the activesheet.

    The values that POPUPTYPE can take are:
    0 : means any cell with a date value on the worksheet can activate the pop-up calendar (this was the default behaviour previously and is the current value of POPUPTYPE in the demo).

    A value of 1 or 2 means only cells in the named range/s can activate the pop-up calendar.
    In addition:
    1 : means any cell with a date value on the worksheet can activate the pop-up calendar.
    2 : means any empty cell or cell with a date value on the worksheet can activate the pop-up calendar. There is a caveat here - the empty cell cannot be part of a range that is centred across a selection.
    There can be up to 26 named ranges which can pop-up the calendar being POPUPA through to POPUPZ.

    Any other numeric value for POPUPTYPE will turn-off the pop-up calendar. If the named variable POPUPTYPE does not exist, the pop-up calendar will also not activate.

    As there are multiple worksheets in this demo, the code to deal with the pop-up calendar on the activesheet has been moved into a new module called SheetPop. In addition, you can now specify a minimum and maximum date to the pop-up calendar at the worksheet level and you can specify if the calendar starts on a Sunday (Monday by default). Look at the comments in the Sheet1 or Sheet2 code.

    So in the wsautrey100 specific example: named range POPUPA would be set to AZ69:AZ77 and named range POPUPB would be set to cell D7 on the activesheet. The value for the named variable POPUPTYPE would be either 1 or 2 (depending on how you want to handle empty cells).

    Update 22-Apr-11: Sorry folks, I noticed that the blurb on the "Range tester" sheet referred to named ranges starting with "DATERNG" instead of "POPUP". I have updated just the wording on this sheet to avoid confusion, there have been no code changes. "DATERNG" was the original prefix I was going to use.

    Re: Lookup & open file based on partial filename

    To answer your previous question:


    1) I was just looking through your code and was curious to figure out how Excel determines which "Private Sub" or "Private Function" to execute first? Also, in which order are these sub-routines/functions being processed (I can't seem to tell by just looking at the code)?

    requires a little understanding of programming in general and how VBA works.

    A module can contain any number of Subs and Functions and in VBA each of these can be in (more or less) any order. Each of these Subs and Functions can be Private or Public (they are Public if neither is declared). The LocateMatchingFiles sub in my code is a good example, it has neither Private nor Public declared but you can see it in the list of macros, otherwise you could never call this code from a command button or userform, etc. You can use Private essentially to hide code no other part of the spreadsheet needs to see. A Private Sub or Function can only be executed by another Sub or Function within the same module.

    Irrespective of whether they are Private or Public, Subs or Functions are executed when required. I am an old Pascal programmer so my Subs and Functions tend to be declared prior to where they are called eg, if I have a Function that needs to call a Sub the code for the Sub is put into the module above the Function. If that Sub calls another Sub, that code is further up the module and so on.

    To understand the order in which these Subs and Functions are processed, look at the code in this thread. In this code there is only one Public sub that can be called (the LocateMatchingFiles sub). You start execution of this code from a command button you have put onto your worksheet. This code executes one line at a time and it "branches" to the Sub or Function that is called based on which line of the LocateMatchingFiles sub you are on eg, one of the first Functions called by LocateMatchingFiles is SetGlobalValues. When execution hits this line of code, the code will branch to that function and return to the next line of code in LocateMatchingFiles when that function has finished executing. This applies to all the other Subs and Functions in the module.

    When you understand more about VBA, use the debug mode to step through each line of code to see how the execution of the code proceeds through the Subs and Functions.

    How you decide to break up a piece of code into a Sub or Function is sort of arbitrary but code that is repeated is a good candidate for a Sub or Function and breaking up a large chuck of code into logical pieces also makes sense. If the pieces are done well, you can often re-use the piece in another project with little or no change. Some of my code in this project can be found elsewhere on the forum in slightly different form.

    Re: Lookup & open file based on partial filename

    Rite-o...based on all the information between this thread and some private messages, this new code should do the trick:

    Note again that there are many changes so best to replace your entire code with this one. Just about everything is "dynamic" now - it does not matter if some of the data moves around a bit.

    I had also allowed for both a "direct" and "running total calculated" pre-tax cash flow value by using the compiler directive "UseRunningTotal" - you can see this in the code. Ignore if you wish or delete everything between the #If and #End If statements (including the # statements themselves).
    If you do delete these, you can also get rid of the #Const UseRunningTotal = False line as well.
    If you keep these and change the value to #Const UseRunningTotal = True, you must recompile the code before you use it.

    Also, I am unable to respond to any more of your private messages as your quota of messages has been reached. I will separately post a response to your question about Private subs, etc. however, if you are new to VBA and want a good introduction get your management to by the book called "Excel 2003 Power Programming with VBA" by John Walkenbach (it may have been updated to 2007 or 2010 by now) instead of paying me out of your own pocket!

    Re: Non-activex Datepicker Calendar Control

    Anything is possible in VBA! As always there would be a number of different ways to implement this.
    Can I assume you are referring to the current activesheet only?
    Also, is it likely that the range of cells will vary over time?
    I would suggest one way to tackle it would be to have named ranges with a common prefix eg, "DATERNG" so your named ranges could be "DATERNG01", DATERNG02", "DATERNG03" etc. That way, the code could see if the activecell is in one of the named ranges and you can change the named ranges any time. The code could then loop through the named ranges using the prefix until it runs out of ranges to check.

    Re: Lookup & open file based on partial filename

    So some important questions first:
    1. You say you want to use the "Updated as of" instead of AM1 - fair enough I am all for removing hard coding where possible. However, I assume the date will always be 1 column to the right of this string. Remember searching for this string is easy but adds to processing overhead slightly.
    2. If AL-AO are not fixed then the easiest thing to do would be to find the first "2011" (in this case) in row 2 and assume this is the start of 4 columns of data. I assume this means you could be adding data which pushes the 2011 running total column further over.
    3. Based on my assumption in point 2, I assume there will only be one column that has the tag "Running Total" in, there won't be a running total for 2011 followed a running total for 2012, etc?

    In response to your reverting back to Pre-Tax cash flow question - this code technically does not exist as you asked for it to be changed to the calculation based on running total. If you need both, you have a number of options from using a named variable to adding a second "starting" macro which decides whether or not to paste a straight value into AL or a calculated value into AL ie, you would have two macro buttons on your master sheet. If this option does not change often, I would go with a named variable which has a TRUE or FALSE value, something like "PRE-TAX-APPLY-CALC = TRUE" (or FALSE).

    At this rate, I think this thread needs to move to the "Hire Help" section, I hope you're not telling your masters this is your work!:)

    Re: Userfom Multiselect Listbox

    The simple answer for your multiselect listbox is to use the RowSource property. You can set the RowSource property to a range anywhere on the worksheet.

    Also, the combobox that holds the year values can be populated based on the sheet names. And because you can change the RowSource, you can change the list of cities when you change the year value (answering your "dynamic" question).

    Concatenating the city names into a single string can be done at the same time the cities are added to the "Input" sheet (answering your last question).

    As always, this is good to read about but a working program is worth a thousand words, so based on a few assumptions, I have cobbled together something that I think answers all those questions. See the attached file and run the demo.

    Re: Lookup & open file based on partial filename

    Many years of coding in other languages is showing I suspect....but thanks anyway!

    You can obviously make the font Arial size 8 manually in the workbook but if you want to add it via code, add to the last part of the GetValues routine:

    Only the bit relating to font setting has been added, the rest of the code is just here as a point of reference.


    Re: Result in a Cell using multiple conditions

    In your specific example you can use an array formula in cell F5, something like:

    To use an array formula type the formula into the cell without the curly brackets and press Ctrl+Shift+Enter instead of just Enter when you have typed the formula. This should result in a value of "19" which is the total of all the type "P" where the ID=1. I am assuming the ID is numeric and the type is a string value (hence the double quotes around the P in the formula).

    Re: Lookup & open file based on partial filename

    From what you have said, the only code change relates to the average invested capital calculation as the rest of the code works for Pre-tax cash flow, ROI and Payout figures (I am not looking for these values in any specific column).

    So, to correct my misinterpretation that the YTD Invested Capital value was 4 columns to the right (when in fact it is 1 column to the left), the only bit of code that needs to change is:

    Actually, only two lines change here: Offset(0, 4) becomes Offset(0, -1) as you mentioned.

    Otherwise, I believe the rest of my assumptions were correct and this should work with these 2 minor changes.

    Re: Merge three spreadsheets with one common column but sometimes different info

    I assume we are talking about 3 separate Excel files (workbooks) and not 3 separate worksheets in one file.

    In either case, the logic would be similar. The trick is to compile a list of the "unique" ID numbers from each of the separate files and then grab the information relative to each ID from each file and paste it all together into another sheet.

    This can be done by creating a non-duplicated collection and then looping through each item in the collection to grab the data that is relevant to that ID on each of the files.

    The attached example probably makes this easier to understand. The code is in the Merge1 file and the other 2 files try to emulate what you've said about the the data in the other files.

    For demo purposes, the data was created just to confirm it can be merged successfully. There is also an assumption that the data is on the first sheet (starting at row 1, column 1) and that the ID# is in column A on each of the workbooks. Row 1 on each workbook is assumed to be a "header" row.

    Unzip all the files into the same folder. Then open the Merge1 file and click the button to create the merged data. The merged data ends up on another sheet in the Merge1 file. The Merge2 and Merge3 files do not have to be open in Excel for this to work.

    Re: Lookup & open file based on partial filename

    If all my assumptions are correct (as per previous post), the following code will do what you need:

    The main change is that the GetPreTaxCashFlow routine has been replaced by the more generic GetValues routine - this seemed to be the best place in the end to perform the data extraction and manipulation.

    There have been a number of other small changes as well so best to delete all the code you have now and replace it with this. The significant other change was moving the DirectoryListToArray outside of the loop through the BUs - this array was unnecessarily being recreated for each BU.

    As the files I have do not have "2010 YTD Invested Capital" or "2011 YTD Invested Capital" tags, this part is hard to confirm. I have manipulated a copy of the 16122 file to test it. Again, the code will work if the assumptions are correct - for the 2011 Ave Invested Capital value, the main assumption is that the data is 4 columns to the right of the tag.

    The rest of the code returned values in columns AN and AO which look OK to me. Column AN appears to be a percentage so I also grabbed the format from the BU file for these values.

    Re: Lookup & open file based on partial filename

    A few points of clarification:
    1. You now say that the value in column AL needs to be modified. It already contains the Pre-tax Cash Flow value but this value needs to be divided by the running total of the working days and then multiplied by Total number of Working days in the Year. Can I assume all this ends up back in column AL?

    2. Another point of clarification here: You say the number of working days for this month is in the master file in Column AW. However, your example is "64" which is actually from column AX. Can I assume you meant column AX as you talk about the running total of working days?

    3. Will the date always be in cell AM1? Will the running total days always be in the range AX4-AX15? it would be easier to use named ranges for these in the event they move around. But then you also have to know the name of the range to make the macro code work.

    4. In the example you sent "2010 YTD Invested Capital" and "2011 YTD Invested Capital" are both in column D but the value associated with these strings appears to be in column H. Will these strings always be in column D and will the value always be in column H. Or will the value associated with these strings always be 4 columns to the right of the string (if the strings are not always in column D)?

    5. Is the string "Return on Capital" unique in each file and, if so, will it always be in column C? Will the value you want to return always be 1 column to the right of the string?

    6. Is the string "Practice Share of Bonus" unique in each file and, if so, will it always be in column C? Will the value you want to return always be 1 column to the right of the string?

    Lastly, by definition, all the extra data you need from the BU files has to be obtained while you have the file open. Otherwise, you are unnecessarily opening and closing files multiple times. So logically the code goes in ProcessMatchedFile routine - before the file is closed of course!

    Re: Conditional Formula for Year to Date Sales Comparison

    A quick solution appears to be putting in an actual "zero" value in the cells that are "skipped". It appears you are trying to use the COUNT function to determine how many months have elapsed but this will only count non-blank cells. This means if FEB for example is "blank", but you have values for JAN and MAR, your count will be 2 but I suspect you want it to be 3.

    So try putting in a "0.00" value into the skipped months, it will definitely change the value you are calculating.

    Also, I notice you are using COUNT from column B to column Q but Q is a "Total" column, did you mean to include this column in your count or should it stop at column P?

    And, of course, your #DIV/0! errors can be fixed with another ISERROR check eg;

    Re: Subtracting from Master sheet

    I am assuming you have 6 sheets labelled "Monday" through to "Saturday" as well as a sheet labelled "Failed" and a master sheet.
    If so, then cell E3 (using your example) on the 'master' sheet needs a formula something like:

    This formula can then be copied to other cells on the 'master' sheet.

    Re: Setfocus on textbox with blank or invalid entry

    Why not use a Keypress subroutine on the textbox to disallow anything but numerics in the first place:

    Private Sub TB01_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
      'allow numerics only
      If (KeyAscii < 48 Or KeyAscii > 57) Then
        KeyAscii = 0
      End If
    End Sub

    Admittedly, this will get messy with 30 textboxes on a UserForm as you have to define a Keypress sub for each one. And, of course, you still have to check for a null value.

    Re: Lookup &amp; open file based on partial filename

    As per our discussions, the final code is below. You need to delete ALL the code you currently have and replace it with this as there are a number of changes.

    If you make the changes to deal with duplicate BUs that we discussed via email, this code should now do exactly what you want.

    Re: Macro to delete all empty sheets from all the workbooks in the folder

    I know some of your request is duplicated in another thread as cytop has indicated.

    As I have seen elsewhere in this thread and the other related thread, the definition of what constitutes an "empty" sheet needs clarification. I have assumed that if there are no "shapes" and if there is no data in any of the cells, then the sheet is empty. "Shapes" includes buttons on the worksheet, comments, charts embedded on the sheet, etc as far as I know.

    I was working on something close to what you were looking for in relation to another thread. I modified the code and put it into the attached sample file. You should test this on copies of files first, NOT originals and if you plan to run it across a whole folder then use a folder that contains copies of files you want to test. Don't run this on original files until you are satisfied it works!

    Whilst I have coded to allow for Excel 2007 and 2010 file types, I do not currently have access to either of these versions to test the code on. The code was written and tested using Excel 2003.

    Files that are password protected will be skipped. Files that cannot be opened for whatever reason will be skipped. Files that are "read-only" will be processed but any changes made won't be saved. Of course, a file that is completely "empty" will still contain at least 1 empty sheet after processing.

    Note that to declare a FileSystemObject you will need to add Microsoft Scripting Runtime in the VBE. While in the VBE go to Tools-->References and check Microsoft Scripting Runtime.