| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
VBA Cell Addressing
I've got what must be a simple addressing problem but it's driving me
crazy. I can create a simple macro that just looks like: Sub TestMacro ActiveCell(1,2) = "Done" End Sub And it does exactly what I'd expect. It plugs a value into the cell to the right of the cursor. Now if I try to do the similar thing with a Function instead of a Macro it just refuses to co-operate. For example: Function TestFunct(CellRef) ActiveCell(1,2) = "Done" End Function This will croak with a #VALUE! error. Likewise: Function TestFunct(CellRef) CellRef(1,2) = "Done" End Function Also refuses to co-operate with the same error. Yet both of these approaches work fine for me in a Macro. How do I write to an arbitrary cell location from a Function? Thanks. Bill |
|
#2
|
|||
|
|||
|
"Bill Martin -- (Remove NOSPAM from address)" wrote in message ... I've got what must be a simple addressing problem but it's driving me crazy. I can create a simple macro that just looks like: Sub TestMacro ActiveCell(1,2) = "Done" End Sub And it does exactly what I'd expect. It plugs a value into the cell to the right of the cursor. Now if I try to do the similar thing with a Function instead of a Macro it just refuses to co-operate. For example: Function TestFunct(CellRef) ActiveCell(1,2) = "Done" End Function This will croak with a #VALUE! error. Likewise: Function TestFunct(CellRef) CellRef(1,2) = "Done" End Function Also refuses to co-operate with the same error. Yet both of these approaches work fine for me in a Macro. How do I write to an arbitrary cell location from a Function? Thanks. Bill You can't write to an arbitraty cell from a function. It has to be a Sub. /Fredrik |
|
#3
|
|||
|
|||
|
Presumably, you are trying to do this from a worksheet? You can't.
A worksheet function can only return a value, it cannot change any of the cell or worksheet attributes. -- HTH RP (remove nothere from the email address if mailing direct) "Bill Martin -- (Remove NOSPAM from address)" wrote in message ... I've got what must be a simple addressing problem but it's driving me crazy. I can create a simple macro that just looks like: Sub TestMacro ActiveCell(1,2) = "Done" End Sub And it does exactly what I'd expect. It plugs a value into the cell to the right of the cursor. Now if I try to do the similar thing with a Function instead of a Macro it just refuses to co-operate. For example: Function TestFunct(CellRef) ActiveCell(1,2) = "Done" End Function This will croak with a #VALUE! error. Likewise: Function TestFunct(CellRef) CellRef(1,2) = "Done" End Function Also refuses to co-operate with the same error. Yet both of these approaches work fine for me in a Macro. How do I write to an arbitrary cell location from a Function? Thanks. Bill |
|
#4
|
|||
|
|||
|
Fredrik Wahlgren wrote:
You can't write to an arbitraty cell from a function. It has to be a Sub. /Fredrik Well, that explains that. Alternatively can I call a Sub from a Function, and have that Sub write to an arbitrary cell? Thanks... Bill |
|
#5
|
|||
|
|||
|
No, see my reply. The best you can do is use the Worksheet_Change event to
monitor a cell, and if it changes, then trigger your changes. -- HTH RP (remove nothere from the email address if mailing direct) "Bill Martin -- (Remove NOSPAM from address)" wrote in message ... Fredrik Wahlgren wrote: You can't write to an arbitraty cell from a function. It has to be a Sub. /Fredrik Well, that explains that. Alternatively can I call a Sub from a Function, and have that Sub write to an arbitrary cell? Thanks... Bill |
|
#6
|
|||
|
|||
|
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
<html <head <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type" </head <body bgcolor="#ffffff" text="#000000" Bob Phillips wrote: <blockquote " type="cite" <pre wrap=""No, see my reply. The best you can do is use the Worksheet_Change event to monitor a cell, and if it changes, then trigger your changes. </pre </blockquote <font face="Arial"Thanks Bob. I'll have to muddle along some other how.<br <br Bill<br </font </body </html |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Indexing / Cell Addressing | Excel Discussion (Misc queries) | |||
| inserting data from a row to a cell, when the row number is specified by a formula in a cell | New Users to Excel | |||
| Addition to Turn cell red if today is greater or equal to date in cell | New Users to Excel | |||
| GET.CELL | Excel Worksheet Functions | |||
| VLookup resulting in a blank cell... | Excel Worksheet Functions | |||
Linear Mode
