• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 428
  • 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
  • 2
1 Solution
Here is a user-defined function for that. Put it in a module and call it from cell e2 as


and f2 as



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

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

End Function

Open in new window

NewToVBAAuthor Commented:
Just perfect, exactly what I was looking for.
Many thanks, Thomas, well done!

Cheers, V
Glad to help. Thanks for the grade.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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