Posts by widgetwonka

• Make my formula copy across columns

Re: Make my formula copy across columns

The INDIRECT() method is probably best. you will need to build the ranges somewhere else (like above the cells), and then use the indirect where you reference the cells.

• Max value from a graph

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.

• Max value from a graph

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:

=MAX(D:D)

And the Max X value for that Y Value is:

=INDEX(C:C,MATCH(MAX(D:D),D:D,1))

Does this work for you?

• Replace Grid Lines

Re: Replace Grid Lines

Jena,

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.

• Replace Grid Lines

Re: Replace Grid Lines

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

Code
Sub RemoveGridLines()
'
ActiveWindow.DisplayGridlines = true
End Sub
• Changing formula based on drop down list

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.

• Change negative numbers to positive numbers

Re: Change negative numbers to positive numbers

Yes. In another cell, type in -1

Then, copy that cell.

Select the column that you want to change and go to edit --> paste special --> multiply

Hit OK.

That should do it.

• how to skip blank cells using a vlookup FORMULA

Re: how to skip blank cells using a vlookup FORMULA

Can you be more specific about the data and what you want the VLOOKUP to accomplish? The title says you want to skip blank cells, but in the post you say you want the VLOOKUP formula to run only on blank cells.

• VBA Array - For Each Running Slowly

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

Rory,

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:

Code
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.

• VBA Array - For Each Running Slowly

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

Rory,

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.

• VBA Array - For Each Running Slowly

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

GlennUK,

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.

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

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

• VBA Array - For Each Running Slowly

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!)

--Jeremy