Posts by michaelnicolas

    Re: Index Match and Vlookup too Slow - Please HELP

    Hi geeasa,

    Have a look at the "Scripting Dictionary" object that could replace your lookup formulas, ozgrid is a great place to do some research.

    Excel samurai Jindon posts a lot of excellent solutions on this object.

    I would go with this approach but if it's too complicated for you consider using the last row method for speeding up your existing routine, e.g in the match formula do not scan the whole of column "B" but rather find the last row in your file, so something like MATCH(C2, ' " & zy & " '!B1:B " & Lastrow, 0).

    Hope that helps,

    Re: Time interval formula issue

    Hi,When you delete a cell then you formula causes a division by zero error (your COUNTIF part):Before any deletion =SUMPRODUCT({FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}/{1;2;2;2;2;1;1})+1After deletion =SUMPRODUCT({TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}/{1;1;0;2;2;1;1})+1When a cell is missing what is the desired output?Best RegardsNicolas

    Re: VLOOKUP with changing number of columns

    Hi mr444,

    Test the macro below.
    To test, delete the VLOOKUP formulas in your worksheet and run GetDailyCodes.
    Your actual workbook's structure should be identical to the one you supplied.

    Hope that helps.
    Best Regards

    Re: VBA Pivot Tables that can be used across all versions

    Hi, I "cooked" up this but I'm not sure if its OK. It runs OK in 2007 but cannot test in other versions.

    Then you use this function when you create the pivot table, e.g.

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
            "Sheet1!R3C3:R11C5", Version:=PivotInstance()).CreatePivotTable _
            TableDestination:="Sheet1!R4C11", TableName:="PivotTable1", DefaultVersion _
        Cells(4, 11).Select

    Hope it helps.
    Best Regards

    Re: Filtering pivottable using VBA


    Best Regards

    Re: Dynamic array in VBA of newly added worksheets

    Just adding to Smallman's suggestion you could:-
    (a) add a prefix to your worksheets e.g. Sys_Example1, Sys_Example2.......(Sys for system)
    (b) the worksheet opens then based on the user response, show the worksheets relevant to them (nothing changes to your current workbook)
    (c) then the process suggested by Smallman will run and pick all the worksheets that are user created, i.e. not starting with the prefix Sys_.

    Just a thought.....
    Best Regards

    Re: Split only numbers to next column

    MID(text, start from character no, number of characters to return), so:
    text = in cell A1
    start from character no = start from 2 (avoiding the starting parenthesis)
    number of characters to return = here it could be a fixed number, e.g. 11 (if the number length is always 11) or variable (which is the case because I didn't knew if the length is standard).

    I used the SEARCH to find where the closing parenthesis starts - 2 (to avoid the space and the closing parenthesis)
    SEARCH(find text, within text; start from character no)

    Hope this helps.

    Re: Multiple IF logic

    Welcome to Ozgrid! Try =IF(B2<=30,"0-30",IF(AND(B2>30,B2<=60),"31-60",IF(AND(B2>60,B2<=90),"61-90",IF(AND(B2>90,B2<=120),"91-120","121+"))))

    Best Regards

    Re: Dynamic SQL Query; selected table based on cell value

    Hi blab,

    Test the attached.

    Best Regards

    Re: Sumifs Formula in VBA using &lt;= and a cell reference

    The line of code you posted has a typing error, it throws a Run-time Error 1004 "Application-defined or object-defined error".
    When I changed from $B2$:$B$ to $B$2:$B$, it worked.
    I will help more if you could upload a small workbook with dummy data but identical structure plus your code.