Vlookup through a range to return cell address

[jweinst4]

Hi,
This is my first time posting, so my sincere apologies if my "syntax" for the post is incorrect. Anyway, I would like to find the max value(s) in a range, and then vlookup(or something else) to return a list of the cell addresses where these max values are found. For example:

Sub findValue()
dim maximum as integer

range("a1:e1")=5
range("a2:e5")=3

maximum=worksheetfunction.max(range("a1:e5"))

End sub


Now, i would like to add code so that i get a list returned to me that says

a1
b1
c1
d1
e1

because these are the cells within the specified range where the max value is found. Again, this is my first post, so sorry if i got anything wrong. I really appreciate your help, and the regulars are doing a great service to us newbies who don't know what we're doing! Thanks a lot.
-Jon

 

[Jonmo1]

Welcome to the board..

Your post was just right, you completely described what you want and provided clear example. Good job.


Now, what is the purpose for returning the list of addresses containing the max value?
Is it strictly just for display purposes, or do you plan on doing something further with those addresses?

I ask because it is generally unnecessary to return the address of a cell to work with it.

It can be done, but it will be a cumbersome unneeded step if you have a larger goal in mind for those cells.

 

[jeffreybrown]

Hi Jon & Welcome to the board,

Not sure I understand exactly what you are tying to do. You seemed to want to look for the max but you are returning all the cells.

Let's start with this...

Sub CheckQty()
    Dim r As Range
    Dim strAddresses As String
    For Each r In Range("A1:E1")
        If r.Value = 5 Then strAddresses = strAddresses & r.Address & vbNewLine
    Next r
    MsgBox strAddresses, vbInformation
End Sub

 

[jweinst4]

The purpose is purely to display the list, nothing more. I am keeping row 1 open, so the idea is to look through the entire range that I specify, lets say b2:h100. It will look for the maximum value of 5, and if there are 4 cells in that range with a value of 5, then in a1, b1, c1, and d1(because there are 4 cells with the maximum value), it will give me the cell address(for example, a1 will show (4,5) or (57,20)). And Jonmo1, I tried that code, and I got a bunch of blank boxes, even though I put 5 in a few of the cells in a range. Again, Thanks for your help!
-Jon

 

[Jonmo1]

OK, so it's strictly for display purposes...

And you want to:

Search B2:H100
Determine the max value of that range
Find each cell containing that max value
And put those addresses in Row 1


Try

Sub FindMax()
Dim MyRange As Range, c As Range, MyCol As Long, MyMax As Long
 
MyCol = 1
Set MyRange = Range("B2:H100")
MyMax = Application.Max(MyRange)
 
For Each c In MyRange
    If c.Value = MyMax Then
        Cells(1,MyCol).Value = c.Address
        MyCol = MyCol + 1
    End If
Next c
End Sub

 

[jeffreybrown]

delete...

 

[jweinst4]

Perfect! You guys were a great help. With your input, I just finished a program where I type in a random string of numbers and characters, it loops through many iterations, and returns me the number of palindromes in which the palindromes are of the largest length. For example, if my input was "kayak", in A1 it would show me the word "kayak". If my input was "amaoplp", in A1 it would show me the word "ama", and in A2, it would show me the word "plp", because the largest palindrome in that list is of length 3, and there are two of them, so it will show me 2 palindromes. Again, thanks for all your help in assisting with the last part of my program!
-Jon