Cell reference

[jarhead58]

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

 

[jarhead58]

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

As I stated in Mark858s post below in post #20, I also did yours and it gave me what I was looking for!! Thank you!!

 

[MARK858]

Happy that you now have a couple of solutions to your problem.

 

[jarhead58]

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!!

Ok, I thought this would give me the result I wanted but it didn't! Trying to get the top 5 numbers which include duplicates! How far off am I?

Sub test
Dim first As Long
Dim second As Long
Dim third As Long
Dim fourth As Long
Dim fifth As Long
Dim firstlocval, secondlocval, thirdlocval, fourthlocval, fifthlocval As Range
Dim firstloc, secondloc, thirdloc, fourthloc, fifthloc As Range
Set rng = Range("Y32:Y57")
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)

For Each cell In rng
If cell.Value > second And cell.Value < first Then second = cell.Value
Set secondloc = Range("Y32:Y57").Find(second, , xlValues, xlWhole, xlByRows, xlNext, False)
Set secondlocval = secondloc.Offset(0, -2)

If cell.Value > third And cell.Value < second Then third = cell.Value
Set thirdloc = Range("Y32:Y57").Find(third, , xlValues, xlWhole, xlByRows, xlNext, False)
Set thirdlocval = thirdloc.Offset(0, -2)

If cell.Value > fourth And cell.Value < third Then fourth = cell.Value
Set fourthloc = Range("Y32:Y57").Find(fourth, , xlValues, xlWhole, xlByRows, xlNext, False)
Set fourthlocval = fourthloc.Offset(0, -2)

If cell.Value > fifth And cell.Value < fourth Then fifth = cell.Value
Set fifthloc = Range("Y32:Y57").Find(fifth, , xlValues, xlWhole, xlByRows, xlNext, False)
Set fifthlocval = fifthloc.Offset(0, -2)
Next cell
MsgBox firstlocval & "," & secondlocval & "," & thirdlocval & "," & fourthlocval & "," & fifthlocval
End Sub

 

[jarhead58]

The code that Mark858 and Eric W both worked! Wanted to try and get more information; top 5 numbers instead of just the MAX number. I tried the following code, along with the working code. The result wasn't what I was looking for! Any help would be appreciated! TIA

Sub test
Dim first As Long
Dim second As Long
Dim third As Long
Dim fourth As Long
Dim fifth As Long
Dim firstlocval, secondlocval, thirdlocval, fourthlocval, fifthlocval As Range
Dim firstloc, secondloc, thirdloc, fourthloc, fifthloc As Range
Set rng = Range("Y32:Y57")
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)

For Each cell In rng
If cell.Value > second And cell.Value < first Then second = cell.Value
Set secondloc = Range("Y32:Y57").Find(second, , xlValues, xlWhole, xlByRows, xlNext, False)
Set secondlocval = secondloc.Offset(0, -2)

If cell.Value > third And cell.Value < second Then third = cell.Value
Set thirdloc = Range("Y32:Y57").Find(third, , xlValues, xlWhole, xlByRows, xlNext, False)
Set thirdlocval = thirdloc.Offset(0, -2)

If cell.Value > fourth And cell.Value < third Then fourth = cell.Value
Set fourthloc = Range("Y32:Y57").Find(fourth, , xlValues, xlWhole, xlByRows, xlNext, False)
Set fourthlocval = fourthloc.Offset(0, -2)

If cell.Value > fifth And cell.Value < fourth Then fifth = cell.Value
Set fifthloc = Range("Y32:Y57").Find(fifth, , xlValues, xlWhole, xlByRows, xlNext, False)
Set fifthlocval = fifthloc.Offset(0, -2)
Next cell
MsgBox firstlocval & "," & secondlocval & "," & thirdlocval & "," & fourthlocval & "," & fifthlocval
End Sub

 

[jarhead58]

Just an FYI! I am putting a similar thread on another site. here is the link! Top 5

 

[MARK858]

Sub test()
    Dim first As Long
    Dim second As Long
    Dim third As Long
    Dim fourth As Long
    Dim fifth As Long

    first = WorksheetFunction.Large(Range("Y32:Y57"), 1)
    second = WorksheetFunction.Large(Range("Y32:Y57"), 2)
    third = WorksheetFunction.Large(Range("Y32:Y57"), 3)
    fourth = WorksheetFunction.Large(Range("Y32:Y57"), 4)
    fifth = WorksheetFunction.Large(Range("Y32:Y57"), 5)



    MsgBox first & "," & second & "," & third & "," & fourth & "," & fifth
End Sub

I am assuming above that your cells do actually contain longs, amend with CLng if actually necessary.


Btw, as a side note you are declaring variables incorrectly, in VBA this

Dim firstlocval, secondlocval, thirdlocval, fourthlocval, fifthlocval As Range

actually says

Dim firstlocval As Variant, secondlocval As Variant, thirdlocval As Variant, fourthlocval As Variant , fifthlocval As Range

it should be

Dim firstlocval As Range, secondlocval As Range, thirdlocval As Range, fourthlocval As Range , fifthlocval As Range

 

[jarhead58]

Sub test()
    Dim first As Long
    Dim second As Long
    Dim third As Long
    Dim fourth As Long
    Dim fifth As Long

    first = WorksheetFunction.Large(Range("Y32:Y57"), 1)
    second = WorksheetFunction.Large(Range("Y32:Y57"), 2)
    third = WorksheetFunction.Large(Range("Y32:Y57"), 3)
    fourth = WorksheetFunction.Large(Range("Y32:Y57"), 4)
    fifth = WorksheetFunction.Large(Range("Y32:Y57"), 5)



    MsgBox first & "," & second & "," & third & "," & fourth & "," & fifth
End Sub

I am assuming above that your cells do actually contain longs, amend with CLng if actually necessary.


Btw, as a side note you are declaring variables incorrectly, in VBA this

Dim firstlocval, secondlocval, thirdlocval, fourthlocval, fifthlocval As Range

actually says

Dim firstlocval As Variant, secondlocval As Variant, thirdlocval As Variant, fourthlocval As Variant , fifthlocval As Range

it should be

Dim firstlocval As Range, secondlocval As Range, thirdlocval As Range, fourthlocval As Range , fifthlocval As Range

I stand corrected

 

[MARK858]

I stand corrected

I assume by the like that the code that the code I posted worked for you?

As for the declaring of the variables I understand why you wrote it the way you did, as in many languages you can write them that way but unfortunately in VBA you to explicitly declare all variables individually or they default to the Variant data type.

 

[jarhead58]

I assume by the like that the code that the code I posted worked for you?

As for the declaring of the variables I understand why you wrote it the way you did, as in many languages you can write them that way but unfortunately in VBA you to explicitly declare all variables individually or they default to the Variant data type.

Actually, the like was for the correction. I'm away from my laptop. I'll check it out when I get back. Thanks

 

[jarhead58]

Actually, the like was for the correction. I'm away from my laptop. I'll check it out when I get back. Thanks

I've tweaked it a little, trying to get it to allow duplicates instead of skipping them. It's not cooperating with me lol. Any suggestions?

New code:

    Dim first As Long
    Dim second As Long
    Dim third As Long
    Dim fourth As Long
    Dim fifth As Long
    Dim firstlocval As Range, secondlocval As Range, thirdlocval As Range, fourthlocval As Range, fifthlocval As Range
    Dim firstloc As Range, secondloc As Range, thirdloc As Range, fourthloc As Range, fifthloc As Range
    Set rng = Range("Y32:Y57")
    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)

     For Each cell In rng
        If cell.Value > second And cell.Value < first Then second = cell.Value
            Set secondloc = Range("Y32:Y57").Find(second, , xlValues, xlWhole, xlByRows, xlNext, False)
            Set secondlocval = secondloc.Offset(0, -2)

        If cell.Value > third And cell.Value < second Then third = cell.Value
            Set thirdloc = Range("Y32:Y57").Find(third, , xlValues, xlWhole, xlByRows, xlNext, False)
            Set thirdlocval = thirdloc.Offset(0, -2)

        If cell.Value > fourth And cell.Value < third Then fourth = cell.Value
            Set fourthloc = Range("Y32:Y57").Find(fourth, , xlValues, xlWhole, xlByRows, xlNext, False)
            Set fourthlocval = fourthloc.Offset(0, -2)

        If cell.Value > fifth And cell.Value < fourth Then fifth = cell.Value
            Set fifthloc = Range("Y32:Y57").Find(fifth, , xlValues, xlWhole, xlByRows, xlNext, False)
            Set fifthlocval = fifthloc.Offset(0, -2)
    Next cell
    MsgBox firstlocval & ", " & secondlocval & ", " & thirdlocval & ", " & fourthlocval & ", " & fifthlocval