Posts by klxracer

    Hi there,


    I've been using an index & match formula which is working perfectly across the first six columns, but won't drag or copy to the other columns. A #REF! result occurs. I've even typed the formula in manually and get the same result.


    I've searched online for possible causes but haven't found a solution.


    The formula is:


    =INDEX('source'!$B$4:$GZ$521,MATCH($A15,'source'!$A$4:$A$521,0),MATCH(L$14,'source'!$B$3:$GZ$3,0),MATCH(L$13,'source'!$B$2:$GZ$2,0))


    The formula has three criteria and they are located in column A and rows 13 and 14.


    Please help!

    Re: Setup Printer In Vba


    Hi Dave,


    Yep, permissions are sufficient to allow this to take place, but I am willing to consider the opinions of those with more experience in these matters than I. Perhaps it might be better to simply check for the printer and notify the user if they need to have it setup.


    The last thing I want is for this to cause more problems than solutions.


    Cheers,



    Paul

    Re: Setup Printer In Vba


    Hi Barry, you are dead right - I want to actually setup a new printer via VBA - without involving the user in any way whatsoever.


    Also - I have tried editing the title after seeing your post - but can't find how this is done (Edit allows me to edit the body of the posting only).

    I have a new workbook that needs to be printed to a specific printer regardless of who the user is. I am happy with the method used for doing this using the PrintOut function, but am trying to work out what to do in the event that the user does not have this printer installed.


    The logic, not in true VBA syntax, is as follows;


    Code
    1. ' Check Printer
    2. If <This Printer> is installed then
    3. Printout using <This Printer>
    4. Else
    5. Install <This Printer>
    6. Printout using <This Printer>


    I found a useful post about ascertaining whether or not a printer is installed, but can't find anything about actually installing a printer via VBA.


    Any suggestions?


    Regards,



    Paul

    Re: Find, Offset, And Update


    Thanks Dave, that seems to have gotten me most of the way there. Still a slight issue in finding the correct cell, but will hack my way through that one.

    Re: Find, Offset, And Update


    Dave, I'm confused. If you haven't seen the code, then how were you able to ascertain whether or not it contained the find function?


    Or, are you referring to the prior iterations I mentioned? If so, here is the old code, followed by the current code without the additional options.



    And, without the options;



    Paul

    Re: Find, Offset, And Update


    Dave, I read the page you mentioned well prior to receiving your first posting. If you are referring to the additional options you outline - these have been tried in prior iterations of the code, however it still doesn't run - so I have excluded anything that didn't 'seem' essential so as to simplify the troubleshooting process.


    I have now put these back in, and tried again, and sure enough - it still doesn't run. In fact, it still gives me the message "Object Variable or With Block Variable Not Set". I have tried shifting the declaration for vfind into the With block, but still I get the error, and I cannot for the life of me see any other variable used within this code blockl.

    Re: Find, Offset, And Update


    I have tried using the Find method already - as shown in the code of the posted example. I have also spend nearly eight hours reviewing a multitude of postings on this site, in an attempt to exhaust all other options before posting.

    Hi,


    Attached is a workbook that I am having a major problem with. Basically it is a stock management system for a hotel allowing purchase orders to be easily generated and receipted.


    The purchase side is working as I want it to - so no problems there.


    On the receipting side, however, I am having a lot of trouble. Here's what I want it to do.


    For each item shown on the Receipt sheet that has a quantity in the received column, I want the code to find the appropriate line in the StockTotals sheet (using the UID which appears in column A of both sheets), then offset the appropriate number of columns, and update the numbers as follows;


    Stock on hand grows by the number receipted.
    Number on order drops by the number receipted.


    If the number on order is now zero, then I want to clear the ordered cell as well as the ordered date cell next to it.


    Any suggestions as to the best way to achieve this?


    Thanks,



    Paul

    Re: Order Results Of Table Extraction


    Hi Dave,


    Forgive my ignorance - but is it possible in a pivot table (via VB) to automatically feed which parameters to display. i.e if June is selected, show only June, and if a particular category is selected, show only that category?


    Am about to embark on trying it - but any suggestions will be welcomed.


    The main reason I have steered away from pivots in the past is the mental barrier a lot of "non-Excel" users seem to have with them. Arrays can be slow when there are a lot of them, but they work 100% of the time and don't get broken by users.


    Cheers,



    Paul

    Hi,


    I am finding that I am often using Max(if(... formulas to pull in Staff ID's that have seen activity in a large data dump. I then use a VLOOKUP to return name based on ID.


    Trouble is, the names end up out of order. I want to be able to order them in one of two ways - either by work done, or alphabetically.


    Any ideas...



    Cheers,



    Paul

    Hi,


    I am trying to put together a quick and dirty "Big Screen" display for a business unit, allowing multiple snapshots of data to be displayed on a large screen much like an airline departure / arrivals board.


    The information to be displayed consists of;


    - Two Excel workbooks (one sheet on each).
    - One external application.


    I want to implement a delay of around 20 seconds between each switch, in order to cycle between all three once every minute.


    The external application is not a Microsoft app, and ideally I would like to implement a handling loop to check if the application is active and restart it if necessary - although this isn't critical.


    Any suggestions?


    Paul

    Hi,


    I am working on a pseudo database application within Excel to allow people to capture some data for review and analysis down the track. Normally would use Access, but this is not an option in this instance.


    See the attached example for exactly what I am trying to achieve - basically I have two stumbling blocks.


    1. Locating data in one field if it already exists and allowing it to be overwritten / updated.


    And


    2. Same as above but allowing the user to delete the row.


    Also wanting to be sure I am clearing the variables correctly?


    Regards




    Paul

    Hi,


    I have a bunch of workbooks that are used to capture information about implants used in surgical cases. I use a small VBA process each morning to update a querytable which contains all of the core patient information for future cases, however it is not working properly.


    The code goes through the process OK, or so it appears, opens and closes each sheet, and saves them appropriately, but the query table doesn't seem to refresh no matter what I do.


    Here's what I have thus far... any suggestions;



    Thanks in advance...


    Paul

    Hi,


    I have a frequent requirement to use a left outer join in MS Query within Excel, and to use more than two tables.


    Whenever I try to do this, I get a message telling me I can't have more than two tables when using a left outer join.


    NB - All other tables are joined with a normal join, running from left to right the first table links to the second with a left outer, then all others flow on from there.


    Any ideas about how I can get around this?


    Regards,


    Paul