Posts by mhabib

  • Milage Report

    Re: Milage Report


    Hi,


    Try using the attached file. I've created dynamic ranges "Mileage_Table", "From_City" and "To_City", and set up validation cells (drop-downs) on each row on sheet "2009" (cols C and D). Finally, mileage in column G is calculated via INDEX/MATCH formula. This might be a simpler solution to writing VBA code to do what you need.


    HTH


    m

  • Retrieve Specific Fields from Worksheet in External Excel Workbook

    Hi,


    I need to retrieve fields "Preiod", "Arrears Rate" and "Refund Rate" from Sheet called "CRA Rates" in an external (closed) workbook.


    I have a data connection, which retrieves ALL fields from the above-mentioned location. However, I only need the 3 fields mentioned above.


    I tried modifying my Query by:
    1) Changing "COMMAND TYPE" to SQL, and
    2) Typing in the command: SELECT 'Period', 'Arrears Rate', 'Refund Rate' FROM 'CRA Rates$';


    Unfortunately, I got syntax errors, and so had to change the command type back to TABLE.


    Would appreciate any kind of help on this - i.e., Query-edit and/or VBA code


    (This used to be way easier/simpler in the good old days of Excel 2000.)


    Thanks,


    m

  • Using INDIRECT with Named Ranges

    Re: Using INDIRECT with Named Ranges


    TABLE_1 =OFFSET('CRA Rates'!$A$2,0,0,COUNTA('CRA Rates'!$A$2:$A$65536),3)


    TABLE_2 =OFFSET('Ontario Rates'!$A$2,0,0,COUNTA('Ontario Rates'!$A$2:$A$65536),3)


    TABLE_3 =OFFSET('Quebec Rates'!$A$2,0,0,COUNTA('Quebec Rates'!$A$2:$A$65536),3)


    TABLE_4 =OFFSET('Alberta Rates'!$A$2,0,0,COUNTA('Alberta Rates'!$A$2:$A$65536),3)


    TABLE_5 =OFFSET('Manitoba Rates'!$A$2,0,0,COUNTA('Manitoba Rates'!$A$2:$A$65536),3)


    TABLE_6 =OFFSET('Saskatchewan Rates'!$A$2,0,0,COUNTA('Saskatchewan Rates'!$A$2:$A$65536),3)

  • Using INDIRECT with Named Ranges

    Re: Using INDIRECT with Named Ranges


    SMC - That's Impressive!


    However, I'm still getting the #REF! error - I forgot to mention that all 6 ranges are Dynamic (used the OFFSET function to define the names). I'm certain that's what's causing the error.


    The VLOOKUP works when I hard-code the range name, but not when I call it using INDIRECT. Can you help?

  • Using INDIRECT with Named Ranges

    Hi All,


    1) I have 6 lookup tables named TABLE_1, TABLE_2 ..., TABLE_6.
    2) User specifies the a lookup value in cell A1, and enters a number between 1 and 6 in cell B2


    Here's what I want:


    If the user inputs "1" in cell B1, then return the result from formula: VLOOKUP(A1,TABLE_1,2,FALSE)
    If the user inputs "2" in cell B1, then return the result from formula: VLOOKUP(A1,TABLE_2,2,FALSE)
    etc.


    I tried setting up my formula as follows, but it doesn't work - Getting #REF! error.


    =VLOOKUP(A1,INDIRECT(CHOOSE(B1,"TABLE_1", "TABLE_2", "TABLE_3", "TABLE_4", "TABLE_5", "TABLE_6")),2,FALSE)


    Apparently, INDIRECT can handle literal cell references (e.g. "A1:B10"), but not Named Ranges (e.g., "TABLE_5").


    Can anyone suggest an alternative - preferably without the use of intermediate cells/computation?


    Thanks,


    m

  • Copy Status Bar Stat To Clipboard

    Re: Copy Status Bar Stat To Clipboard


    Fenclif: Thanks for the help. The macros are working now. (I don't have an uncle Bob - but if you insist ...)


    Andy: Thanks. Your code did the trick.


    Poplen: Your code works great for sums.


    One last question: Is there a way to programmatically include the MS Forms 2.0 Reference?


    Thanks.


    m

  • Copy Status Bar Stat To Clipboard

    Re: Copy Status Bar Stat To Clipboard


    I'm getting the following error on both the macros:


    "Compile error: User-defined type not defined".


    Is there a special add-in that I need to include for the code to work?


    m

  • Copy Status Bar Stat To Clipboard

    Hi All,


    I want to create a macro that will copy the stat that is displayed in the status bar (bottom right corner - e.g. Sum, Count, Max, Min, etc.) into the clipboard in order to paste it to another workbook or worksheet.


    At present, I'm creating a formula on the active sheet and pasting the value to the detination sheet/workbook. Since I have to do this many times over, I'd prefer to have a macro to copy the value in the status bar to the clipboard, and simply pasting.


    Thanks,


    m

  • Embed Add-in In A Word Document

    Re: Embed Add-in In A Word Document


    Hello Jack,


    I would like my Add-in to follow the Word document that I've created. In other words, I do not want to refer to a network location for my Add-in that may become invalid in future.


    The advantage of doing it this way is that whoever takes over the task after me will receive a Word document containing the procedural instructions for the task, along with the necessary UDF. When I took over this task, I got the instructions as Word attachment via email, and that's how my successors will get it as well.


    Given the constraints, I guess the only option available to me is to place the add-in on the network and provide reference to it in the word file.


    BTW, I can access my UDFs by simply double-clicking the add-in's Icon - i.e., no need to go to Tools > Add-ins. I was hoping the same strategy would work for the next person taking over the task.


    Thanks for your feedback.



    m

  • Embed Add-in In A Word Document

    Hi all,


    I've just written up an SOP for a reconciliation process. Part of it requires the reader to use some Excel UDFs that I've created. Rather than having someone look for the UDF file on the network, I tried to embed it as an Excel Worksheet Icon within my procedure document. However, when I double-click the icon to launch the add-in, I get the following error message:


    "Microsoft Word can't start the application required to open this object. An error occurred and this feature is no longer functioning properly. Would you like to repair this feature now?"


    This does not happen when I embed a regular Excel workbook, or even a template.


    Would appreciate some help on this.


    Thanks


    m

  • Comparing Two Values & Indicate Trend

    Re: Comparing Two Values And Indicate Trend?


    Welcome to the Ozgrid Excel/VBA Forum.




    Try using Conditional Formatting:


    1. Select the cell containing the October value


    2. Click Format > Conditional Formatting


    3. Click the between dropdown and select greater than


    4. Click inside the adjacent placeholder to the right, and click on the cell containing the september value. Be sure to convert the absolute reference to relative (in case the format is to be applied to other cells as well) by pressing the F4 function key until the dollar-signs disappear.


    5. Click the Format button, and apply the desired formatting scheme


    6. Click OK


    7. Click Add button


    8. Repeat steps 3-6 for the second condition


    9. Click OK



    HTH


    m

  • Access Databse Connections in VBA

    Re: DB Connections in VBA


    Try Application.Sendkeys


    Example:


    Application.Sendkeys("mhabib~") will generate keystrokes
    mhabib followed by the Enter key.


    Be sure to follow up the SendKeys command with the DoEvents command to avoid unpredictable outcomes.


    Hence:


    Application.Sendkeys("mhabib~")
    DoEvents


    Symbols for special keys (ALT, TAB, etc.) can be found in the help section.


    HTH


    m

  • Cannot Run Inputbox type 8 more than once in a procedure

    Hi all,


    I cannot run the Type 8 inputbox (for user specified ranges) more than once in my procedure.


    In the code below, the first inputbox for rngSource works fine, but clicking the ok button after selecting a range for the second inputbox (rngDest) pops up the following error:


    Quote


    Run-time error '424':


    Object required


    I initially thought that the Default option was to blame. So I created a dummy procedure with the command for the first inputbox repeated twice, and got the same error.


    Any help would be much appreciated.


    Thanks


    m