Function to Place Value in Specified Cell
 

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(F2210,SetCellVal(F23,"BUY"),SetCellVal(F23,"S ELL")).

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.

Function to Place Value in Specified Cell
 

Craig

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

You may need to look at a worksheet Change macr

--
Message posted from http://www.ExcelForum.com

Function to Place Value in Specified Cell
 

Craig,

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

Put a formula in F23:

=IF(F2210,"BUY","SELL")

HTH,
Bernie
MS Excel MVP

"Craig Bender" wrote in message
...
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(F2210,SetCellVal(F23,"BUY"),SetCellVal(F23,"S ELL")).

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.

Function to Place Value in Specified Cell
 

Sorry, my example should have said
=IF(F2210,SetCellVal(F23,"BUY"),SetCellVal(F24,"S ELL")). 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.


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Craig,

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

Put a formula in F23:

=IF(F2210,"BUY","SELL")

HTH,
Bernie
MS Excel MVP

"Craig Bender" wrote in message
...
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(F2210,SetCellVal(F23,"BUY"),SetCellVal(F23,"S ELL")).

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.

Function to Place Value in Specified Cell
 

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

"Craig Bender" wrote in message
...
Sorry, my example should have said
=IF(F2210,SetCellVal(F23,"BUY"),SetCellVal(F24,"S ELL")). 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.


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Craig,

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

Put a formula in F23:

=IF(F2210,"BUY","SELL")

HTH,
Bernie
MS Excel MVP

"Craig Bender" wrote in message
...
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(F2210,SetCellVal(F23,"BUY"),SetCellVal(F23,"S ELL")).

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.

Function to Place Value in Specified Cell
 

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

Craig.


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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

"Craig Bender" wrote in message
...
Sorry, my example should have said
=IF(F2210,SetCellVal(F23,"BUY"),SetCellVal(F24,"S ELL")). 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.


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Craig,

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

Put a formula in F23:

=IF(F2210,"BUY","SELL")

HTH,
Bernie
MS Excel MVP

"Craig Bender" wrote in message
...
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(F2210,SetCellVal(F23,"BUY"),SetCellVal(F23,"S ELL")).

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.