Cell reference

[jarhead58]

Good morning!
How can I get the cell address of a cell value that is found using worksheetfunction.Max? TIA

 

[jarhead58]

The code produces "9" which is in Y50. W50, what I am trying to find, is "3". Instead, it gives me the value of W39.

W39 is "15". Now if "first" is "9" I need to get the address of that cell that "9" is in which would be "firstloc" or $Y$50. Then I want to offset from that cell same row 2 cols left or W and show me that value in a msgbox.

 

[MARK858]

The 3rd question was what is the value of Y39.

And can you give me the result of the other code that I posted.

 

[jarhead58]

The 3rd question was what is the value of Y39.

And can you give me the result of the other code that I posted.

Sorry, missed that somehow! Y39 is "2". Definitely not the Max

 

[jarhead58]

The code produced "9" as it should. The numbers range from 1 to 9 in my range of Y32:Y57; duplicates of some which is expected.

 

[MARK858]

The code in post number 10 shouldn't produce a 9 as that according to what you posted is in Y50. The code is offsetting by -2 columns and so should be giving a 3 from W50.

Set firstlocval = firstloc.Offset(0, -2)
MsgBox (firstlocval)

Do you have a 9 in column W, if yes what cell? and what is the value of column Y on the same row?

 

[Eric W]

I don't know what your overall goal is, but for this limited purpose, it appears that what you're doing can be handled with a basic worksheet formula. Either of these should handle it:

Sub test99()

    MsgBox Evaluate("INDEX(W32:W57,MATCH(MAX(Y32:Y57),Y32:Y57,0))")
        
    With WorksheetFunction
        MsgBox .Index(Range("W32:W57"), .Match(.Max(Range("Y32:Y57")), Range("Y32:Y57"), 0))
    End With

End Sub

 

[jarhead58]

The code in post number 10 shouldn't produce a 9 as that according to what you posted is in Y50. The code is offsetting by -2 columns and so should be giving a 3 from W50.

Set firstlocval = firstloc.Offset(0, -2)
MsgBox (firstlocval)

Do you have a 9 in column W, if yes what cell? and what is the value of column Y on the same row?

Ok, in Col Y from row 32 thru 57 these are the values. 6,2,6,2,7,4,4,2,5,7,5,5,6,2,1,4,4,4,9,4,3,1,3,3,0,6 respectively. Col W same rows. 6,20,2,24,18,25,11,15,23,14,13,8,21,17,9,26,1,19,3,10,7,5,12,22,29,4 respectively. Now, I'm not sure if this makes a difference since I am just looking for a value but numbers in both columns are results of formulas within the cells.

 

[jarhead58]

Mark858; I knew this was definitely Monday!! I pasted the wrong code, apologies! Let me run that code that you had in post 10.

15 was the result


In answer to your question in post 15; Yes there is a 9 in col W in row 46, Y46 has 1.

 

[MARK858]

Out of interest does the below make a difference?

Sub eTest()
    Dim first As Long
    Dim firstlocval As Range
    Dim firstloc As Range
    first = CLng(WorksheetFunction.Max(Range("Y32:Y57")))
    Set firstloc = Range("Y32:Y57").Find(first, , xlValues, xlWhole, xlByRows, xlNext, False)
    Set firstlocval = firstloc.Offset(0, -2)
    MsgBox (firstlocval)
End Sub

 

[jarhead58]

Out of interest does the below make a difference?

Sub eTest()
    Dim first As Long
    Dim firstlocval As Range
    Dim firstloc As Range
    first = CLng(WorksheetFunction.Max(Range("Y32:Y57")))
    Set firstloc = Range("Y32:Y57").Find(first, , xlValues, xlWhole, xlByRows, xlNext, False)
    Set firstlocval = firstloc.Offset(0, -2)
    MsgBox (firstlocval)
End Sub

That actually gave me the result that I was looking for!

Eric W, I tried yours and it also gave me the result that I was looking for.

Thank you guys!!