Hi Experts,

I have a requirement of identifyign the max value cell and then changing the max cell value in excell sheet with a calculated value.

At present i am using the following code, where i am using message box to identify the max value cell and new calculated value. after that i am changing the value manually.

However i want to update the max value cell with the new calculated value.

Pleae help


Sub test_maxadj()

Dim rng As Range

Dim dblMax As Double

Set rng = Range("c2", Range("c65536").End(xlUp))

dblMax = Application.WorksheetFunction.Max(rng)

MsgBox dblMax

MsgBox AddRangeMax([c2:c65536])

MsgBox dblMax + Range("d2")

End Sub

Function AddRangeMax(r As Range) As String
Dim dblMax As Double, c As Range
dblMax = WorksheetFunction.Max(r)
For Each c In r
If c.Value = dblMax And VarType(c) = 5 Then
AddRangeMax = c.Address

Exit Function
End If
Next c
AddRangeMax = "NA"
End Function