Posts by Arthur494

You're welcome. Those were the days

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: Set Order Of Userform Controls Loop

Thanks for your reply. That's what I suspected.

EDIT BY ADMIN
Question doesn't fit the Thread Titles

I am using stuff from loops http://www.ozgrid.com/VBA/control-loop.htm to loop through all the controls on a UserForm. But I can't find a way of controlling the order in which this is done. I tried both TabIndex and moving the controls around without success. Is there a property of a control that controls the loop order, please?
tia

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: Delete Rows After Vlookup

Quick answer is not to use VLOOKUP at all, but FIND instead. Note the row number from the FIND, copy your value across and then delete the row.

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: Employee Holiday Clash Of Dates

Since more than one department could have a clash on any given day, I have assumed that you will want to know which departments are affected.

Re: Variable Percentage Commissions Calculator

Hi and welcome to OzGrid!

Have a look to see if the attached does what you want. Let us know if it's unclear.

I'm not sure of your condition. I have assumed that the one-third of basic cut applies both to the commission percentage and to the commission basis itself.

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":

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

Thread

Excel statistical functions: TREND

Excel statistical functions: TREND

Describes the TREND function in Excel 2003 and in later versions of Excel, illustrates how the function is used, and compares the results of the function in later versions of Excel ...

Dave Hawley

Apr 11th 2008

! 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: Dates Distribution Between Time Periods

Give this a whirl!

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:

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: Store Visible Number Of Visible Rows Into A Variable

Why don't you just use COUNTIF() with the criterion used in the Autofilter, and add 1 for the header row?

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

Here's a VBA answer:

Sub Wyoming()
Dim c As Range
Dim FirstAddress As String


With Worksheets(1).Range("b1:b500")
    Set c = .Find("Y261", LookIn:=xlValues)
    If Not c Is Nothing Then
        FirstAddress = c.Address
        Do
            Set c = .FindNext(c)
            If c.Offset(0, -1).Value > 999 Then c.Value = "Y265" Else c.Value = "C063/Y202"
        Loop While Not c Is Nothing And c.Address <> FirstAddress
    End If
End With
End Sub

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?

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

Your question and example are not very clear on one point.
Are Y261, Y265 and "C063/Y202" values or cell addresses?