Excel Macros

[Legion]

Any Excel experts out there?

If i define a dynamic range like this


Function box(usearea As range)


How do i get the machine to realise the last cell in the range?

[Samadhi]

I am an expert. I'm really not sure what you're asking though, what exactly do you want to do?
It would really help if you posted the code and explained what you're trying to do.

[This message has been edited by Samadhi (edited 02-05-2004 12:32 PM).]

[Legion]

All I'm really trying to do, is write a set of routines to work out statistical values which are not available as a standard Excel routine. For the purpose of discussion, lets say I wanted to find the third highest number in the range. I need some way of reading all the values in the range without exceeding the range itself, perhaps some kind of loop which stops when it reaches the end of the range, but how can the routine know when it has reached the end?

[Samadhi]

How does the data get in there? Copy and paste? Why not just set the range to the selected (highlighted) area?

[Macros]

oh, its not a thread of encouragement....

[Samadhi]

[Mercuria]

heh... i thought the same thing macros did...

when you say "range," do you mean the range of the statistical data, or the "area" from which you are reading the statistical data?

[nepenthe]

Why not devote a large area of the sheet (more than you're ever likely to need) as the range? The function will ignore by default any cells that are empty, provided you're looking for a specific value.

[Eykir]

Legion: A Range object in a Excel defines both a first and a last cell. You just need to extract them from the Range object. Check the help that comes with it.

[Beartalon]

Eykir beat me to it.

Also, if you are programming this to incorporate range changes, you'll have to write a function that changes the range using the SetRange function. At the moment, I can't reach my programming books (more than 20 feet away and my bed is closer) - maybe later.

[Legion]

Thanks for the answers, however none of them are really helping.

If we think about the 'sum' function within Excel, we write, 'sum(' then drag over the range to sum, then type ')' and the machine adds them all up for us. The function must have a for loop which add all the numbers up, how does it know where to stop?

Essentially, How would would you write the 'sum' function?

[Samadhi]

Well, if you actually know the range you can write a loop. If for some weird reason you actually want to do that. That's not how it does it though. It goes through the collection. By definition when it gets to the end of the range it's done. It doesn't need to loop.

[Beartalon]

If you're using VBA, use a For Each loop, set to look at each cells in the range, and it will automatically stop when it has read all the cells in the range.

[Legion]

That looks like what I'm looking for bearty, any cahnce of a quick line of code to see what it might look like?

[Beartalon]

Dim cellX as Cell
Dim rngY as Range

'plus any other variables you need and code setup

Set rngY = ThisWorkbook.Sheets("TotalOrders").Range("A1:B5")

For each cellX in rngY

' do your calculations here

Next

A1:B5 can be replaced with a named range.

[Legion]

Looks to be exactly what I need. Especially that 'each cell' bit.

Hearty thanks Bearty