Posts by widgetwonka

    Re: Max value from a graph

    Do you have a trendline for this plot? Is the equation quadratic? Can you display the regression equation, then take the first derivative and solve for zero? I can't envision another scenario.

    Please post an example if you can.

    Re: Max value from a graph

    Firstly, let's assume that your Y values are not multi-modal.

    Next, let's assume that your X Values are in column C and your Y values are in column D

    So, the max Y value is:


    And the Max X value for that Y Value is:


    Does this work for you?

    Re: Replace Grid Lines


    gridlines are just a fancy type of border. You can add them to a range without the code:

    1. select the range you want to add them to
    2. go to format --> cells --> border
    3. select the color in the bottom right of the first block (grey - 25%)
    4. apply to the interior and exterior using the appropriate buttons

    If you have to do this continually, you can use a module in the VBE editor, but you might not want to go the code route for just this.

    Re: Replace Grid Lines

    Does tools --> options --> view --> gridlines (check and apply) work?

    If not, what about:

    Sub RemoveGridLines()
        ActiveWindow.DisplayGridlines = true
    End Sub

    Re: Changing formula based on drop down list

    1. Use a drop down box to drive the value in a cell (the non-vba dropdown).
    2. Use a lookup to decode the target cell into the region that you want to use in the sumproduct.
    3. Drive the sumproduct off the lookup result.

    This would be easier if you could post an example formula. Is Japan further segmented into cities, etc? You could also handle this w/o VBA, and that would be easier to update without having to go to the code when new breakdowns are added.

    Re: Need Help with VBA Array - For Each Running Slowly


    I had not thought about condensing to one line. I suppose I used the variable when I was writing the formula to make it easier for me. But, now that I have all the pieces I need, I can write it directly:

    Public Function FRONGELLO(Current As Double, portreturns As Range, BenchReturn As Double, Previous As Double) As Double
    'Thanks to Rory & GlennUK on Ozgrid for helping me sort this mess
    FRONGELLO = (Evaluate("PRODUCT(1+ " & portreturns.Address & ")") * Current) + (BenchReturn * Previous) + Previous
    End Function

    Thanks for all the help. Hopefully I will be able to pay it forward one day.

    Re: Need Help with VBA Array - For Each Running Slowly


    I was unaware that using a double would return a decimal. I had been having problems with my formula evaluating to an integer when I first wrote it, so I thought the variables were the problem. Using your tip, I have written it as compactly as I think I can. I went ahead and swtiched to a single, since these numbers are quite small, and I only have five decimal points of precision on the inputs anyway.

    Thanks to both of you for your help.

    Any other thoughts would also be appreciated.

    Re: Need Help with VBA Array - For Each Running Slowly


    Thanks for the advice. That has sped it up about 50%

    New code:

    Anyone have any idea about the decimal? I have read (and tried) to declare a variable as decimal, but that is not accepted, which is why I have the CDec() code in there.

    Re: Adding a sheet and then referencing the new sheet in an existing code

    Is the number of sheets to add undetermined? If so, why not something like

    I'm a long time lurker, first time poster to Ozgrid. So, hello all, and thanks for all the help to date.

    My question is around how to improve my code so that it takes less time to run.

    I have a UDF that uses a for each statement to calculate an array. Here is the code:

    I can accomplish this more quickly with an array formula in Excel, but then I lose the ability of the user to interpret the fact that this formula is a specific algorithm (known as the frongello alogrithm). Also, if a user wants to use the algorithm and this formula isn't defined, then they have to know how to use an array formula...easier said than done in this crowd...

    Really, this code could probably be improved in two ways:

    1) is there a better way to have the formula evaluate as a decimal? I realize that declaring my variables as variant is taking up more space than is probably neccesary, but I am not sure of the proper method

    2) is there a more efficient way to write an array formula in VBA that mimics {=PRODUCT(1+"range")}

    I have been an Excel user for a long time, but I am fairly new to VBA, so any help would be appreciated. I have been reading several programming guides to get up to speed (and Ozgrid obviously!)