Posts by Arthur494

    It is with some trepidation that I post this request as I have some difficulty in describing it in one line. So if I've got it wrong I hope someone will put it right.


    My client has a very large database. I have successfully written code for Excel in MS VBA which creates and sends a query string through the internet to the database and handles the answer (value or string) when it comes back. In that case the query string and the answer coexist on the spreadsheet in different cells.


    He now wants a more complex system where the query string and the answer coexist in the same cell, such that the user can modify the string, send it to the database and see the answer in the same cell. I see this as analogous to having a formula in the formula bar, e.g. "=2^3+6-SQRT(9)", and the answer, "11", in the cell.


    In my case the formula bar might contain "=QueryString(GrowthRate, Slovenia, 1Q2004, 3Q2006)" and the cell, eventually, "+12.4%".


    This is beyond my ken, so I have to ask firstly whether it is possible and, if so, what kind of solution I should be looking for.


    Thanks in Advance.

    Re: Multiple Sum Queries With Multiple Criteria


    Thanks, Krishnakumar, I hadn't spotted that possibility! What I was hoping for was some way of including a DSUM() directly in an ordinary table using its row labels as criteria and without the need for column labels.


    However I have found it possible to tuck the criteria range away and make reference to it in my table, which works fine. The result is that a recalculation which took 2.327 seconds in the book with array formulae now takes 0.125 seconds.


    Which goes to prove that array formulae do take time!

    The attachment contains a data table from which I am trying to get all the information according to multiple criteria.


    In the real model the data table contains only up to 400 rows, but there are nine fruits, seven types and nineteen countries, making 285 combinations (types only apply to apples).


    As each year is a separate field I can't find a way to make it amenable to a pivot table.


    I can handle the multiple criteria, but am having difficulty finding the best way to make multiple simultaneous queries.


    The way most obvious to me is to use array formula of the kind "=sum((country="France")*(fruit="Apple")*(Type="Pippin")*1997)". This works fine, but it is quite slow in recalculating, and not favoured by some(!)


    My example uses multiple DSUMs, which works well in the example, though it's not very elegant. I'm flummoxed by the need to include a set of column labels with every individual query.


    Is there a better way, please?


    tia

    Re: Flag Employee Holiday Date Clashes


    Quote

    could this also show the employees affected too and the dates


    See the attached which shades cells when more than one person in a single department is on holiday. I'm not sure how you want the dates shown?


    btw for the cognoscenti: I think I've done something I thought was impossible, namely to use an array formula in a conditional format; it doesn't display curly brackets but seems to work!

    Re: Combining An If Statement With A Loop


    Yes, we do it all the time!


    But make sure the Do... Loop is contained within the If... End If construct.


    And you can't write "Do If", use "Do While" or "Do Until":


    Code
    If ....... Then 
        Do While (Until) ....... 
        Loop
    Else ....... 
    End If


    For more info. do a search for "Do Loop" on the OzGrid!

    Re: Dates Distribution Between Time Periods


    Because TD is the NAME of the cell A2!


    If you do not understand the use of named ranges in Excel look at the Help page "Define named cell references or ranges" or do a search on "Named Ranges" in this website.

    Re: Dates Distribution Between Time Periods


    Thanks Turtle44 :)


    I was just obeying

    ! Today's date is in a cell to which I have given the name "TD". I could have written "TODAY()" in place of "TD" throughout and got the same answer.

    Re: Copy And Manipulate Text In Cell


    Try: "=A1&SUBSTITUTE(MID(A1,FIND("[",A1),FIND("]",A1)-FIND("[",A1)+1)," ","+")"


    Note that this formula assumes that your original string contains the characters "[" and "]". If not, it will return "#VALUE!".


    In which case try this: "=IF(NOT(OR(ISERROR(FIND("[",A1)),ISERROR(FIND("]",A1)))),A1&SUBSTITUTE(MID(A1,FIND("[",A1),FIND("]",A1)-FIND("[",A1)+1)," ","+"),"error")"


    Which only leaves the case when "]" precedes "[". I'll leave some other bright spark to work that one in!

    Re: converting Minutes Into Fractional Hours


    You did not respond to Sicarii's question.


    However, if you are using a time format, as in "08:20:00", multiplying by 24 will give the answer "8.33" in number format with two decimal places. Excel stores time as fractions of a day, so multiplying by 24 give fractions of an hour.

    Re: Store Visible Number Of Visible Rows Into A Variable


    Well, suppose your database range is A1:J200, and you set your Autofilter to "Dakota" in column A. Then the in, say, cell A202 the function "=COUNTIF(A1:A200,"Dakota")" will return the number of rows which contain "Dakota" in column A. Add 1 if you want your total to include the header row.


    If you want to do the same in VBA code, COUNTIF() is one of the Excel functions you can use:

    Code
    MyTotal = Application.WorksheetFunction.COUNTIF(range("A1:A200"),"Dakota") + 1

    will achieve the same effect, though you will probably have a better way of defining the range.


    In VBA Help, search for "WorksheetFunction Property" and "List of Worksheet Functions Available to Visual Basic".

    Re: Macro To Determine Value Of A Cell Based On Criteria


    Here's a VBA answer:


    This is adapted from the VBA Help example for FIND. But, has anyone else found that the example doesn't work because the statements in the Do Loop are reversed?