Referencing the same cell address while copying a formula

[exceltyro]

I have a range of cells with some values. I then have a MAX function to find
the maximum of those values.

Now I want to use the cell address containing the maximum value in new
function and range of cells.

My question is how to reference the max.value cell without changing its cell
address (while copying in the new range of cells) ???

 

[Gary''s Student]

say the original range is an aribtrary collection of cells. Try:

Function foo(rng As Range) As String
Dim c As Range, x As Double
x = Application.WorksheetFunction.Max(rng)
For Each c In rng
If c = x Then
foo = c.Address(0, 0)
Exit Function
End If
Next c
End Function


(from Harlan Grove)

 

[exceltyro]

Thanks Gary's Student.

I think I did not put my question very clearly. Sorry about that. I found
out (by accident) preceding cell address with a $ sign does what I wanted.

Thanks for your response.