How to update a target cell within a formula cell 1

[Cotton9]

Attempting to write a function or cell formula that will update a target cell, B132, if the current cell, B133, is larger.

Cell B133 contains #Value! after function reaches the .Value = nNew assignment. If B132 is greater than MAX(B6:B130) then all is well and B133 displays the value for MAX(B6:B130), the smaller value as it should. In debug mode all variables show the same type.

The range B6:B130 is a range of share prices with B132 being the high water mark overtime to hold, not just the max( B6:B130).


B132 is the high water mark spanning months, B6: B130 of time
B133 is the cell to contain the formula

spreadsheet sample values

B6 10.347
.
.
.
.
B130 115.027

B132 4
B133 =+SetHighWaterMark( CELL("address", B$132), CELL("contents", B$132), MAX( B$6:B$130 ))

=======================

‘ Test High Water Mark function
Sub hwm()
Dim nX
‘ This works and returns the correct value and
' sets the target cell
‘ and returns the correct value. 4 in this case.
nX = SetHighWaterMark("$B$132", 1, 4)
End Sub
‘’--- EOS hwm()


Function SetHighWaterMark(Target, nLast, nNew) As Double

Dim nCurVal
If nLast < nNew Then
Stop
' This returns the correct value contained in
' Target cell (as expected)
nCurVal = Range(Target).Value

‘ this fails if called from within a cell
' no error message detectable
Range(Target).Value = nNew
End If
SetHighWaterMark = nNew

End Function
‘’--- EOF SetHighWaterMark()

==============================

Thanks,

Daryl

 

[SkipVought]

hi,

If I understand you correctly, you want to update another cell (B132) from a different cell (B133).

This cannot be done with a function. A function used on a sheet, can ONLY return a value to the cell in which it resides!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Cotton9]

Skip,

Thanks for the quick reply. I had hope I was just missing some 'little' thing. I'll add the needed functiionality to the download/update functions.

Thanks,

Daryl

Sometimes the short cut is not so short.

D. Buckman
US Army Corps of Engineers, Omaha

Learn from the past, Live in the present, Create the future

 

[SkipVought]

You could do something like
[tt]
B133: =MIN(B132,MAX(B6:B130))
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Cotton9]

Skip,

Understood. What we will most likely do is set B133 to MAX(B6:B130) then in the update function check if B133 is larger than B132 and if so move the "value" of B133 into B132. That way if we purge the B6:B130 table B132 will hold the highest high water mark for as long as we need.


D. Buckman
US Army Corps of Engineers, Omaha

Learn from the past, Live in the present, Create the future

 

[Loomah]

if i'm understanding this correctly you require a static maximum value no matter what the changes are in the range b6:b130?

if that is the case then take advantage of circular references and iteration.

in cell b133 enter the formula = max(b6:b130, b133)
you will get xl tring to tell you you have done something wrog and don't know what you are doing. click ok and whatever else to get rid of that.

then go tools>options choose the calculations tab and ensure iteration is checked.

you will have to delete and recreate the formula when you need to reset it for a new period - that bit you could probably program.

if i've misunderstood your problem then sorry but this may be of some use to you at some point anyway!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[Cotton9]

Loomah,

Yes, that appears to be what I was trying to do. I was trying to make a simple thing a complicated simple thing.

D. Buckman
US Army Corps of Engineers, Omaha

Learn from the past, Live in the present, Create the future