Solved

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

Posted on 2011-03-16
3
410 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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
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 use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

758 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now