Function to Place Value in Specified Cell

C

Craig Bender

This should be real easy, but I having difficulty. I want a function that
places a value in another cell. An example use might be like:
=IF(F22>10,SetCellVal(F23,"BUY"),SetCellVal(F23,"SELL")).

Though, I tried a dozen or so variations it presently looks like:

Function SetCellVal(targe_cell As Range, target_value As String)

'Set Cell to Value
target_cell.Value = target_value

End Function

I guess its more than a one liner.

In advance Thanks for any Help given.

Craig.
 
M

mudraker

Craig

A function can not change the value in any other cell.

You may need to look at a worksheet Change macr
 
B

Bernie Deitrick

Craig,

Functions can only change the value of the cell they are called by:
everything else is ignored.

Put a formula in F23:

=IF(F22>10,"BUY","SELL")

HTH,
Bernie
MS Excel MVP
 
C

Craig Bender

Sorry, my example should have said
=IF(F22>10,SetCellVal(F23,"BUY"),SetCellVal(F24,"SELL")). As of course I
could just use the formula you stated. But real problem is a little harder
then the simple example I stated. Really what this is doing is setting a
flag.

Picture cells A10 & B10. (The entire column of A and B is a running sum of
another going list over a time period, A10 & B10, A11 & B11, etc..but just
focus on 1 period Row 10) They are both doing indepentant calculations,
however, there is one case where I don't want B10 to sum in this value 'X',
instead I want 'X' to be summed into A10 along with the other stuff A10 is
alread summing. Therefore, my idea is in B10's sumif statement it will not
SUM with X instead it will set cell C10 to be 'X'. This way A10 will sum +
C10. C10 will always be 0 except when its not.

So based on the result of my sumif I wanted to populate the adjectant cell.
How do I do that if not in a function?

Thanks,

Craig.
 
B

Bernie Deitrick

Craig,

You can use the worksheet's calculate or change event.

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
If Range("F22").Value > 10 Then
Range("F23").Value = "Buy"
Range("F24").Value = "Sell"
Else
Range("F23").Value = ""
Range("F24").Value = ""
End If
Application.EnableEvents = True
End Sub

Copy the code above, right click the sheet tab, select "View Code", and
paste the code in the window that appears.

HTH,
Bernie
MS Excel MVP
 
C

Craig Bender

Thanks...I'll give it a try.

Craig.


Bernie Deitrick said:
Craig,

You can use the worksheet's calculate or change event.

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
If Range("F22").Value > 10 Then
Range("F23").Value = "Buy"
Range("F24").Value = "Sell"
Else
Range("F23").Value = ""
Range("F24").Value = ""
End If
Application.EnableEvents = True
End Sub

Copy the code above, right click the sheet tab, select "View Code", and
paste the code in the window that appears.

HTH,
Bernie
MS Excel MVP

sum
Click to expand...
 
Top