Solved

Excel VBA - Find Address of a Cell with Largest Value in a Category

Posted on 2011-03-16
3
416 Views
Last Modified: 2012-05-11
Hi Experts! How do I find address of a cell with largest value per category of cells in a range, using VBA? I have a large file and doing it through a formula takes too much RAM, and takes too much time. "=MAX(IF($A$2:$A$33=D2,$B$2:$B$33))", so I want to do it with an efficient macro.

Cheers, N2V
Address-of-Cell-with-Largest-Val.xls
0
Comment
Question by:NewToVBA
  • 2
3 Comments
 
LVL 39

Accepted Solution

by:
nutsch earned 250 total points
ID: 35152419
Here is a user-defined function for that. Put it in a module and call it from cell e2 as

=getmax(D2,$A$2:$B$33,2)

and f2 as

=getmax(D2,$A$2:$B$33,2,1)

Thomas

Function getMax(strCat As String, rgLookup As Range, lgColumn As Long, Optional blAddress As Boolean = False)
Dim rg As Range, dblValue As Double, rgMax As Range

For Each rg In rgLookup.Columns(1).Cells
    If rg = strCat Then
        If rg.Offset(0, lgColumn - 1) > dblValue Then
            Set rgMax = rg.Offset(0, lgColumn - 1)
            dblValue = rgMax
        End If
    End If
Next

If blAddress = True Then getMax = rgMax.Address Else getMax = rgMax.Value

End Function

Open in new window

0
 

Author Closing Comment

by:NewToVBA
ID: 35153113
Just perfect, exactly what I was looking for.
Many thanks, Thomas, well done!

Cheers, V
0
 
LVL 39

Expert Comment

by:nutsch
ID: 35153140
Glad to help. Thanks for the grade.

Thomas
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question