Problem using the Large function in VB

[Alf]

I use this macro to rank the 15 highest numbers in a range of values.

Please Login or Register  to view this content.

It works fine if the cell contain a value i.e. 18298.9 but if it’s a formula like "D4=C4*B4"
I get “Run-time error ‘91’: Object variable or With block variable not set”

I’ve tried to add “LookIn:=xlFormulas” and “LookAt:=xlPart” but I still get the same error message.

Of course I could add a helper column converting formulas to values but I was hoping to find a solution as the Large function works with formulas in a “normal” Excel environment.

Alf

[tigeravatar]

Alf,

I'm not sure why you wouldn't just use the formula, but here's a modified version of your macro. The .Find method needs to use xlValues to find values resulting from formulas. I also included a change to provide unique ranking just in case there are any duplicate numbers in the top 15:

Please Login or Register  to view this content.

[tigeravatar]

Alf,

Alternate, condensed version using the macro to input the formula, and then replacing the formula with its resulting values:

Please Login or Register  to view this content.

[Alf]

Hi

Thanks for help. I've used your second macro as it was short & compact.

I'm not sure why you wouldn't just use the formula

I wanted to place ranking number next to the appropriate value and did not manage to solve that just by using the formula.

Regards

Alf