Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Range
Hi,
If I have VBA Function, what is the correct statement to insert a value into a particular cell, let say cell A1 ? Function ABC ((S1 As String) As String ' how can I set the value at particular cell ? Range("A1").value = S1 ... ... End Function is this Range("A1").value = S1 correct ? or Application.Range("A1").value = S1 ? or... ? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Range
I would try to be as specific as possible:
activesheet.Range("A1").value = S1 or worksheets("sheet999").Range("A1").value = S1 or activeworkbook.worksheets("sheet999").Range("A1"). value = S1 or someworkbookvariablehere.worksheets("sheet999").Ra nge("A1").value = S1 Just a word of warning. If you're thinking about using this function in a worksheet cell, then it won't work. A function called from a cell on a worksheet can't change the value in another cell. Essentially, it can only return a value to the cell with the formula. magix wrote: Hi, If I have VBA Function, what is the correct statement to insert a value into a particular cell, let say cell A1 ? Function ABC ((S1 As String) As String ' how can I set the value at particular cell ? Range("A1").value = S1 ... ... End Function is this Range("A1").value = S1 correct ? or Application.Range("A1").value = S1 ? or... ? -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Range
Hi Dave,
I think my scenario fall under your warning statement. It doens't work with the suggestion. I just want to set a value in another cell. You said ="A function called from a cell on a worksheet can't change the value in another cell." Then how can I change the value in another cell after this function has been executed in a worksheet cell ? Thanks. Regards, Magix "Dave Peterson" wrote in message ... I would try to be as specific as possible: activesheet.Range("A1").value = S1 or worksheets("sheet999").Range("A1").value = S1 or activeworkbook.worksheets("sheet999").Range("A1"). value = S1 or someworkbookvariablehere.worksheets("sheet999").Ra nge("A1").value = S1 Just a word of warning. If you're thinking about using this function in a worksheet cell, then it won't work. A function called from a cell on a worksheet can't change the value in another cell. Essentially, it can only return a value to the cell with the formula. magix wrote: Hi, If I have VBA Function, what is the correct statement to insert a value into a particular cell, let say cell A1 ? Function ABC ((S1 As String) As String ' how can I set the value at particular cell ? Range("A1").value = S1 ... ... End Function is this Range("A1").value = S1 correct ? or Application.Range("A1").value = S1 ? or... ? -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Range
You can't use a formula to change the value in another cell.
You could use a Sub (subroutine, not Function) that does the work. In fact, excel has some builtin events that you may be able to tie into. But it depends on what you want. You could tie into the worksheet_change event if the user is typing something that causes the change. You could tie into the worksheet_calculate if a formula reevaluates and causes the change. Some references: David McRitchie's intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm You can read more about events at: Chip Pearson's site: http://www.cpearson.com/excel/events.htm David McRitchie's site: http://www.mvps.org/dmcritchie/excel/event.htm magix wrote: Hi Dave, I think my scenario fall under your warning statement. It doens't work with the suggestion. I just want to set a value in another cell. You said ="A function called from a cell on a worksheet can't change the value in another cell." Then how can I change the value in another cell after this function has been executed in a worksheet cell ? Thanks. Regards, Magix "Dave Peterson" wrote in message ... I would try to be as specific as possible: activesheet.Range("A1").value = S1 or worksheets("sheet999").Range("A1").value = S1 or activeworkbook.worksheets("sheet999").Range("A1"). value = S1 or someworkbookvariablehere.worksheets("sheet999").Ra nge("A1").value = S1 Just a word of warning. If you're thinking about using this function in a worksheet cell, then it won't work. A function called from a cell on a worksheet can't change the value in another cell. Essentially, it can only return a value to the cell with the formula. magix wrote: Hi, If I have VBA Function, what is the correct statement to insert a value into a particular cell, let say cell A1 ? Function ABC ((S1 As String) As String ' how can I set the value at particular cell ? Range("A1").value = S1 ... ... End Function is this Range("A1").value = S1 correct ? or Application.Range("A1").value = S1 ? or... ? -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Range
Or put a formula into the cell that's supposed to change based on the first
cell. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Dave Peterson" wrote in message ... You can't use a formula to change the value in another cell. You could use a Sub (subroutine, not Function) that does the work. In fact, excel has some builtin events that you may be able to tie into. But it depends on what you want. You could tie into the worksheet_change event if the user is typing something that causes the change. You could tie into the worksheet_calculate if a formula reevaluates and causes the change. Some references: David McRitchie's intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm You can read more about events at: Chip Pearson's site: http://www.cpearson.com/excel/events.htm David McRitchie's site: http://www.mvps.org/dmcritchie/excel/event.htm magix wrote: Hi Dave, I think my scenario fall under your warning statement. It doens't work with the suggestion. I just want to set a value in another cell. You said ="A function called from a cell on a worksheet can't change the value in another cell." Then how can I change the value in another cell after this function has been executed in a worksheet cell ? Thanks. Regards, Magix "Dave Peterson" wrote in message ... I would try to be as specific as possible: activesheet.Range("A1").value = S1 or worksheets("sheet999").Range("A1").value = S1 or activeworkbook.worksheets("sheet999").Range("A1"). value = S1 or someworkbookvariablehere.worksheets("sheet999").Ra nge("A1").value = S1 Just a word of warning. If you're thinking about using this function in a worksheet cell, then it won't work. A function called from a cell on a worksheet can't change the value in another cell. Essentially, it can only return a value to the cell with the formula. magix wrote: Hi, If I have VBA Function, what is the correct statement to insert a value into a particular cell, let say cell A1 ? Function ABC ((S1 As String) As String ' how can I set the value at particular cell ? Range("A1").value = S1 ... ... End Function is this Range("A1").value = S1 correct ? or Application.Range("A1").value = S1 ? or... ? -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I enter formula sum(range+range)*0.15 sumif(range=3) | Excel Discussion (Misc queries) | |||
Excel Addin:Setting the range to the Excel.Range object range prop | Excel Worksheet Functions | |||
Range Question / error 1004: method Range of object Worksheet has failed | Excel Programming | |||
Range.Find returns cell outside of range when range set to single cell | Excel Programming | |||
how to? set my range= my UDF argument (range vs. value in range) [advanced?] | Excel Programming |