Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use cell_ref in place of an item in get pivot data function | Excel Discussion (Misc queries) | |||
I want to place a checkbox in the middle of a cell, yet I want thecheckboxArea take up the entire cell. | Excel Discussion (Misc queries) | |||
How can I place more than 30 arguments in an average function? | New Users to Excel | |||
Look up function best place | Excel Worksheet Functions | |||
"IF" function, have the facility to place a colour in the cell | Excel Worksheet Functions |