Can't Update Cells from a Function?
 

I have a function for which I'd like to update some cells
programmatically. For the purposes of simplicity, I've
boiled the problem down to:

Function CellUpdt(InR as Range, OutC as Range) As Integer
' Do some processing with InR
...

' Update some spreadsheet cells
Cells(OutCell.Row, OutCell.Column) = "Value1"

' Return
CellUpdt = 0
End Function

The function is driven from the spreadsheet itself, ie.,
=CellUpdt(M24:P50,R7:R10).

I can't seem to get this work. In the debugger, when I
reach the 1st assignment statement, the program just
ends - no errors, no messages.

Anybody know what's wrong?

Thanks, Tommy

Can't Update Cells from a Function?
 

Hi Tommy
a function (UDF) can't do this. You are not able to change cells or
formats or other things. You can only return values

Frank

Tommy wrote:
I have a function for which I'd like to update some cells
programmatically. For the purposes of simplicity, I've
boiled the problem down to:

Function CellUpdt(InR as Range, OutC as Range) As Integer
' Do some processing with InR
...

' Update some spreadsheet cells
Cells(OutCell.Row, OutCell.Column) = "Value1"

' Return
CellUpdt = 0
End Function

The function is driven from the spreadsheet itself, ie.,
=CellUpdt(M24:P50,R7:R10).

I can't seem to get this work. In the debugger, when I
reach the 1st assignment statement, the program just
ends - no errors, no messages.

Anybody know what's wrong?

Thanks, Tommy

Can't Update Cells from a Function?
 

Tommy,

The design is unsound - you can't do what you want to do. A worksheet
function can return a value to the calling cell, it can change other cells
as you are trying to do.

For this, you need to use the Worksheet_Change event code.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Tommy" wrote in message
...
I have a function for which I'd like to update some cells
programmatically. For the purposes of simplicity, I've
boiled the problem down to:

Function CellUpdt(InR as Range, OutC as Range) As Integer
' Do some processing with InR
...

' Update some spreadsheet cells
Cells(OutCell.Row, OutCell.Column) = "Value1"

' Return
CellUpdt = 0
End Function

The function is driven from the spreadsheet itself, ie.,
=CellUpdt(M24:P50,R7:R10).

I can't seem to get this work. In the debugger, when I
reach the 1st assignment statement, the program just
ends - no errors, no messages.

Anybody know what's wrong?

Thanks, Tommy

Can't Update Cells from a Function?
 

Thanks to all. Looks like I'll have to change my design.

-----Original Message-----
I have a function for which I'd like to update some
cells
programmatically. For the purposes of simplicity, I've
boiled the problem down to:

Function CellUpdt(InR as Range, OutC as Range) As Integer
' Do some processing with InR
...

' Update some spreadsheet cells
Cells(OutCell.Row, OutCell.Column) = "Value1"

' Return
CellUpdt = 0
End Function

The function is driven from the spreadsheet itself, ie.,
=CellUpdt(M24:P50,R7:R10).

I can't seem to get this work. In the debugger, when I
reach the 1st assignment statement, the program just
ends - no errors, no messages.

Anybody know what's wrong?

Thanks, Tommy
.