Posts by JimFuller1

    Re: User Form To Add Parts Into Columns


    Boynsy,


    Take a look at these two statements and see if they do what you want.


    Code
    iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    iCol = ws.Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1).Column


    Jim

    Re: 2-dimensional Date Range With Dynamic Copy And Paste


    Quote from epynephrin

    I'm afraid not. Maybe I missed that part of the Pivot Table tutorial (MS Online help). Where would one tell the Pivot to use a dynamic range?


    In Step Two of the Pivot Table Wizard, Excel will ask for the range that covers the data to be used by the Pivot Table. You would type the dynamic range name you created for your data into the dialog box.


    Quote

    And, can I add participants in? I'm assuming so, because my Add Participant userform inserts a row above the first empty one for new participants--but this won't screw up the Pivot Table setup?


    The Pivot Table uses a "Dynamic" range. If you look up dynamic range names, you'll find that the reason you use a "dynamic" range is so it adjusts itself to the ever changing size of the data range - increasing or decreasing in size as needed. So, yes, you will be able to add participants without concern that they will be included in the Pivot Table.


    Quote

    Once I've gotten all that down, can I use the cells in the pivot exactly as I would any other cell in Excel (for VBA codes)?


    I'm not sure I would say that all Pivot Table cells can be used exactly like all other Excel cells so, I would have to say it depends. What do you have in mind? After you get the dynamic range set up and the Pivot Table to refer to it, you should be able to answer this question better. You may not need any VBA code.


    Jim

    Re: Large Data Set Summing Macro


    swatb98,


    I would not think you need a macro yet. The built in Excel functionality for subtotals may provide an even simpler answer. You may have to add a helper column to identify the amount you want summed for a top 25 lender.


    Jim

    Re: 2-dimensional Date Range With Dynamic Copy And Paste


    epynephrin,


    Looked at the previous post though I missed it when it happened. I was struck first off by the fact that nobody suggested using a Pivot Table to generate the reports so they don't actually contain any formulas. Could you set up two sheets each with a Pivot Table that references the database of course participants, and use VBA to copy the results of the queries to one report page? I mean, you've got a Cringe formula and a lot of time invested, but I think they are a substitute for what a native Pivot Table will do.


    In my world, I would even insist that the users learn how to minipulate the Pivot Table (in read only mode) to create the results they want. That would accomplish two things. More Excel knowledge in the office (never a bad thing), and the user may discover different questions to ask that your report doesn't cover.... yet.


    Jim


    PS-I hope Foreman quits don't you?

    Re: Force Cells To Recalculate (simulate Double Click?)


    Plus, you should be sure you're using a statement like unto this:

    Code
    ActiveCell.FormulaR1C1 = "=IF(Blah,Blah,Blah)"


    or else you could be writing text to the cell which requires Excel to "RE" evaluate the cell (the double clicking).


    Jim

    Re: Search From A File


    JohnRW,


    You're welcome. Here's what you need then.


    A macro that will:


    1) Search a folder for filenames that match a list in an Excel workbook.
    2) Move the files that match to another folder.
    3) Write an error log to another sheet in the workbook when there is no match.


    Start by searching the forum for the following terms.
    1) Search a folder
    2) Move files
    3) List files
    4) Anything that shows as a related topic when you read something from the search results.


    If you still can't find anything, start another thread using the three items for the macro as the requirements and a suitable title.


    Jim

    Re: Search From A File


    JohnRW,


    More progress... let's see if we can clarify a little bit more.


    1) The CD images will be stored in a Windows folder named "IMFILES".
    2) The image files will be named XXXXXXXXXX-yyyyyy.TIF.
    3) The XXs in the file names will correspond to the voucher number in column A of your spreadsheet.
    4) When there is a match between the voucher number in column A and one of the filenames in the folder IMFILES, you want to copy the matching image file to another Windows folder named "IMFILEMATCH".
    5) You want to use another program, not Excel, to print the files in the folder IMFILEMATCH.
    6) If you don't find one of the voucher numbers in the folder IMFILES, you want an error log printed on an Excel sheet for review later.


    Jim

    Re: Matching Parent Range To Child Ranges (and Match Sizes)


    cochese,


    If the boss has decided how the REPORT should look, that shouldn't keep you from using my second idea. Collect all the data in one place and then create the reports that match the REPORT the boss likes based on the one source of data.


    If, on top of specifying how the reports should look, the boss has said how the data collection should look, you might descretely question him/her on whether she/he realizes that the data collection tool/page/sheet does not have to be the same as the reporting tool/page/sheet. That is, after all, why Excel is so useful. It's easy to format, AND you can do formulas and queries and other neat stuff.


    Jim

    Re: Search From A File


    JohnRW,


    You're making this veeerrrryyyy difficult. Answer each and every question carefully. Read the last post again. Check all my statements for accuracy. Answer all my questions. Post again.


    I'm trying to clear this up enough to get you some help but, you are not making it easy.


    Jim

    Re: Search From A File


    JohnRW,


    Ok. Let's see what we have now.


    1) The CD images will be stored in one file - lets call it "IMFILE".
    2) The IMFILE will contain a list of names that correspond to the voucher number in column A of your Excel file.
    3) The IMFILE will NOT contain anything else but a list of names.
    4) You want to search the IMFILE for voucher numbers that appear in your Excel file.
    5) You want to do something if there is a match.


    What kind of files will the IMFILE be?
    What is the layout of the IMFILE?
    Where will the Voucher Numbers appear in the IMFILE?
    What happens after you find a match?


    Jim

    Re: Search From A File


    JohnRW,


    It's getting more clear but still is quite muddy. Let's see what I got so far and you can add to the list...


    1) The CD images will be named and stored in files.
    2) The list of Voucher Numbers in column A of you spreadsheet will exist in one of the files containing the names of the images.
    3) You want to search the files listing the images for a match.
    4) When you find a match...


    What kind of files will the images be stored in?
    What is the layout of the files?
    Where will the Voucher Numbers appear in the files?
    What happens after you find a match?


    Jim

    Re: Search From A File


    JohnRW,


    I'm guessing that you haven't explained the problem well enough to get the help. Can you post a small sample with the expected results?


    Jim

    Re: Microsoft Cdo For Nts 2.1 Is Missing


    This appears to be a reference to an object used by VBA. In the Visual Basic Editor if you look at [Tools][References...] you will see a list of "References" that includes the dlls mentioned above. I wonder what the actual code causing the problem was....


    Anyway, it's always good to here a problem has been solved. Thanks for sharing the solution ecawilkinson.


    Jim

    Re: Best Way To Organise Headcounts?


    omnikron32,


    1) To get the list of unique cities, use the [Data][Filter][Advanced Filter...] command from the Excel menus. After you have the list, you can write the formula to refer to the cell the city name is in instead of naming the city in the formula.


    =Countif(d2:d4500,a2) (Where a2 is the location of the cell with "Boston" in it)


    2)(a) To countif under multiple conditions you'll probably need to use sumproduct.


    =sumproduct((a2=$d$2:$d$45)*(b2=$e$2:$e$45)*(c2=$f$2:$f$45)) (Where a2, b2, c2 are cells holding the unique combinations of teams, divisions, and cities)


    2)(b) To do this automatically, you'll need to plug some really fancy formulas into a preselected area of an unused sheet (post your example and I'll bet we can do it) or you'll need a macro.


    Jim