The Grey Labyrinth is a collection of puzzles, riddles, mind games, paradoxes and other intellectually challenging diversions. Related topics: puzzle games, logic puzzles, lateral thinking puzzles, philosophy, mind benders, brain teasers, word problems, conundrums, 3d puzzles, spatial reasoning, intelligence tests, mathematical diversions, paradoxes, physics problems, reasoning, math, science.

Author Message
Legion
Daedalian Member

 Posted: Thu Feb 05, 2004 2:50 pm    Post subject: 1 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?
+1

 Posted: Thu Feb 05, 2004 5:31 pm    Post subject: 2 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
Daedalian Member

 Posted: Thu Feb 05, 2004 5:37 pm    Post subject: 3 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?
+1

 Posted: Thu Feb 05, 2004 6:17 pm    Post subject: 4 How does the data get in there? Copy and paste? Why not just set the range to the selected (highlighted) area?
Macros
Daedalian Member

 Posted: Thu Feb 05, 2004 6:42 pm    Post subject: 5 oh, its not a thread of encouragement....
+1

 Posted: Thu Feb 05, 2004 6:42 pm    Post subject: 6
Mercuria
Merc's Husband's Wife!

 Posted: Thu Feb 05, 2004 7:32 pm    Post subject: 7 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
Daedalian Member

 Posted: Thu Feb 05, 2004 7:45 pm    Post subject: 8 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
DDR Freak

 Posted: Fri Feb 06, 2004 3:11 am    Post subject: 9 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
'Party line' kind of guy

 Posted: Fri Feb 06, 2004 5:26 am    Post subject: 10 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
Daedalian Member

 Posted: Fri Feb 06, 2004 10:29 am    Post subject: 11 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?
+1

 Posted: Fri Feb 06, 2004 7:37 pm    Post subject: 12 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
'Party line' kind of guy

 Posted: Sat Feb 07, 2004 1:28 pm    Post subject: 13 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
Daedalian Member

 Posted: Mon Feb 09, 2004 11:45 am    Post subject: 14 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
'Party line' kind of guy

 Posted: Wed Feb 11, 2004 10:08 pm    Post subject: 15 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
Daedalian Member

 Posted: Thu Feb 12, 2004 10:59 am    Post subject: 16 Looks to be exactly what I need. Especially that 'each cell' bit. Hearty thanks Bearty
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year by All usersBeartalonEykirLegionMacrosMercurianepentheSamadhi Oldest FirstNewest First
 All times are GMT Page 1 of 1

 Jump to: Select a forum Puzzles and Games----------------Grey Labyrinth PuzzlesVisitor Submitted PuzzlesVisitor GamesMafia Games Miscellaneous----------------Off-TopicVisitor Submitted NewsScience, Art, and CulturePoll Tournaments Administration----------------Grey Labyrinth NewsFeature Requests / Site Problems
You cannot post new topics in this forum
You can reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum