What I'm trying to do is generate a 2D array of values in VBA from data in a spreadsheet. (I know how to generate 2D arrays and don't need help with that.)
What I would like to do next is look at each row or column of the 2D array and find the max value. I've tried using the worksheetfunction.MAX to no avail. Any help would be greatly appreciated.
Hello marrott2,
Here is an example of how to get the max value of either an entire row or column of an array. The advantage of Application over WorksheetFunction is Application will not throw an error. It will return an error value instead. You can for an error by using the IsError() statement in VBA.
Please Login or Register to view this content.
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Starbelow the post.
3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
Works like a charm. Thanks for the quick help. I guess I need to brush up on my indexing functions.
Hello marrott2,
You're welcome. Glad that it worked on the Mac. I wasn't sure it would.
I have Office for Mac 2011, so almost all of the functionality of VBA works like usual.
Any idea how to possibly get the location of the max value in an row/column in an array?
Hello marrott2,
The keyword is almost.
Hello marrott2,
Try this...
Please Login or Register to view this content.
Tried that already but won't return a location within the arrayOriginally Posted by Leith Ross
Try this...
Please Login or Register to view this content.
Hello marrott2,
Sorry, Loc is a reserved word. Try this...
Please Login or Register to view this content.
Leith,
I knew Loc was reserved and didn't use it. Still not getting it to work. It is coming up with a mismatch error 2042 when I try and print to a msgbox or #N/A when I print to spreadsheet.
Hello marrott2,
Error 2042 is the Excel #N/A error. It seems that there was no match found in the array. The example works fine in Windows. It returns a 2 for the second element in the array.
Leith,
Figured out my problem
I needed to define the range to search in the Match function as the particular row or column where the max was located not the entire array.Please Login or Register to view this content.
Hello marrott2,
Excellent! (There was much rejoicing as they ate Robin's minstrels.)
* and there was much rejoicing..... yaaayyyyyy
Thanks again.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks