• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 426
  • Last Modified:

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

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
NewToVBA
Asked:
NewToVBA
  • 2
1 Solution
 
nutschCommented:
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
 
NewToVBAAuthor Commented:
Just perfect, exactly what I was looking for.
Many thanks, Thomas, well done!

Cheers, V
0
 
nutschCommented:
Glad to help. Thanks for the grade.

Thomas
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now