Cell reference

[jarhead58]

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

 

[Eric W]

It depends on how you're using .Max. Here's one way:

Sub test2()

    x = WorksheetFunction.Max(Range("M:M"))
    MsgBox Range("M:M").Find(x).Address
    
End Sub

 

[Snakehips]

Or possibly like below, with formula?

Book1
	A	B
1
2	M	Max Address
3	1	$A$7
4	2
5	99
6	3
7	263
8	5
9	6
10	nd
11	88
x

Cell Formulas
Range	Formula
B3	B3	=CELL("address",INDEX(A1:A12,SUMPRODUCT((A1:A12=MAX(A1:A12))*(ROW(A1:A12))),1))

 

[jarhead58]

Or possibly like below, with formula?

Book1
	A	B
1
2	M	Max Address
3	1	$A$7
4	2
5	99
6	3
7	263
8	5
9	6
10	nd
11	88
x

Cell Formulas
Range	Formula
B3	B3	=CELL("address",INDEX(A1:A12,SUMPRODUCT((A1:A12=MAX(A1:A12))*(ROW(A1:A12))),1))

Thanks for the reply but Eric W put me in the right direction!!

 

[jarhead58]

It depends on how you're using .Max. Here's one way:

Sub test2()

    x = WorksheetFunction.Max(Range("M:M"))
    MsgBox Range("M:M").Find(x).Address
   
End Sub

It depends on how you're using .Max. Here's one way:

Sub test2()

    x = WorksheetFunction.Max(Range("M:M"))
    MsgBox Range("M:M").Find(x).Address
   
End Sub

That works great! Now when I try to Set firstloc = Range("Y32:Y57").Find(first).Address,
I get a type mismatch error from .Address. I'm just trying to get the value from the cell 2 columns to the left of the "first" address. What am I missing?

Dim rng As Range, cell As Range
    Dim first As String
    Dim firstlocval As Range
    Dim firstloc As Range
    Dim second As String
    Dim third As String
    Dim fourth As String
    Dim fifth As String
    
    Set rng = Range("Y32:Y57")
    first = WorksheetFunction.Max(Range("Y32:Y57"))
    Set firstloc = Range("Y32:Y57").Find(first).Address
    Set firstlocval = firstloc.Offset(0, -2)
    MsgBox (firstlocval)

 

[Eric W]

If you want to point firstloc at the cell, take the Address method off:

Set firstloc = Range("Y32:Y57").Find(first)

 

[jarhead58]

If you want to point firstloc at the cell, take the Address method off:

Set firstloc = Range("Y32:Y57").Find(first)

Hmm...it returns "15", which is in col W row 39 while my "first" is in col Y row 50. I should be getting "3" which is 2 cells to the left of "first" or W 50

 

[MARK858]

What value is in Y39?
What Value is in Y50?
What does the code below produce?

Sub cTest()
    Dim first As String
    first = WorksheetFunction.Max(Range("Y32:Y57"))
    MsgBox first
End Sub

I suspect that you will need to specify certain parameters in the Find.

 

[jarhead58]

What value is in Y39?
What Value is in Y50?
What does the code below produce?

Sub cTest()
    Dim first As String
    first = WorksheetFunction.Max(Range("Y32:Y57"))
    MsgBox first
End Sub

I suspect that you will need to specify certain parameters in the Find.

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.

 

[MARK858]

There were 3 questions, you have answered 2.

and what does the code below produce?

Sub dTest()
Dim first As Long
Dim firstlocval As Range
Dim firstloc As Range 
first = WorksheetFunction.Max(Range("Y32:Y57"))
Set firstloc = Range("Y32:Y57").Find(first)
Set firstlocval = firstloc.Offset(0, -2)
MsgBox (firstlocval)
End Sub