[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
Solved

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

Posted on 2011-03-16
Medium Priority
424 Views
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
0
Question by:NewToVBA
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2

LVL 39

Accepted Solution

nutsch earned 1000 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
``````
0

Author Closing Comment

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

Cheers, V
0

LVL 39

Expert Comment

ID: 35153140

Thomas
0

## Featured Post

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
###### Suggested Courses
Course of the Month13 days, 3 hours left to enroll