One Before Max

[JoeMicro]

Hi, is there a way i could get in vba one before the max of a field?

ex.
Column
2500
3200
1500
5700

so i want to able to get into a variable 3200, and then 2500, etc.. basically i should be able to specify how for backwards from the max i want to go..

i hope his makes any sense.

Any ideas??

Joe.

 

[SkipVought]

hi,

in VBA? Why not using SQL?

select max(a.column)
from [Your Table] a
having max(a.column) < (select Max(b.column) from [Your Table] b)
[code]


Skip,
[sub]
[glasses]Just traded in my [b]old subtlety[/b]...
for a [b]NUANCE![/b][tongue][/sub]

 

[JoeMicro]

Because i need to pull it into a loop and play with each one individually.

and just sorting the table in order and looping through it will make things more complicated..

let me try to explain the concept i am trying to do, maybe that will help...

i have this quantity ex 75843 i want to divide that into the highest factor ex. 3200 the rest i will divide into 2500 and so on. ad i need to do it in vba because the 75843 is created on the fly based on other vba calculations, so i don't think that a query will do the job.

 

[MakeItSo]

SQL would still do in this case.
Pull something like this into a recordset:

Select [yourcolumn]
From [$Sheet1]
Order by [yourcolumn] DESC

Then start looping from the second entry of the recordset.

Cheers,
MakeItSo

“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[JoeMicro]

Tnx, MakeItSo, that works great.