Selecting the Cell with the maximum value

[hercat]

Hello All, I'm writing a code where I want to select the cell with the maximum value.


I can get the address of that cell by using
=CELL("address",INDEX(C1:C360,MATCH(MAX(C1:C360),C1:C360,0)))

but this only works when I paste it into the spreadsheet directly. I want to use it in a VBA code, but when I try to use this to control the active cell, or even set it to be pasted into the spreadsheet to be used later, I get nothing but errors.

What I need is a line something like

ActiveCell.address = CELL("address",INDEX(C1:C360,MATCH(MAX(C1:C360),C1:C360,0)))

or anything that serves an equivalent function.

Any help you can offer is appreciated.

 

[JoeMo]

Hello All, I'm writing a code where I want to select the cell with the maximum value.


I can get the address of that cell by using
=CELL("address",INDEX(C1:C360,MATCH(MAX(C1:C360),C1:C360,0)))


but this only works when I paste it into the spreadsheet directly. I want to use it in a VBA code, but when I try to use this to control the active cell, or even set it to be pasted into the spreadsheet to be used later, I get nothing but errors.

What I need is a line something like

ActiveCell.address = CELL("address",INDEX(C1:C360,MATCH(MAX(C1:C360),C1:C360,0)))

or anything that serves an equivalent function.

Any help you can offer is appreciated.

Try this:

Sub test()
Dim myAddress As String
myAddress = Application.Evaluate("CELL(""address"",INDEX(C1:C360,MATCH(MAX(C1:C360),C1:C360,0)))")
MsgBox myAddress
End Sub

 

[hercat]

Hi Joe, this worked for the first part, but not for the selection of the relevant cell. I figured out how to make it work from your help above though, so thank you!

MaxVal = Application.Evaluate("CELL(""row"",INDEX(C1:C360,MATCH(MAX(C1:C360),C1:C360,0)))")
ActiveSheet.Cells(MaxVal, 1).Copy