Set Value of Cell from function

  • Thread starter Thread starter jason BALDWIN
  • Start date Start date
J

jason BALDWIN

Hi,

Im having trouble setting the value of a cell in a worsheet e.g.

cells("A1").value = 10

Does not set the value of the cell and the function returns #value.

When this line is commented out te rest works fine. How do I return values
to range of cells from within a function?

Thanks,

Jason
 
One minor reason is that Cells requires a row and column argument.
Either use

Range("A1").Value = 10

or

Cells(1, 1).Value = 10

A major problem, though, is that a function called from the worksheet,
whether built in or UDF, cannot change the value in cell other than the
one that called the function.

So

Cells(1, 1).Value = 10

will never work inside a UDF.

To return values to a range of cells, you need to call the function from
that range, either as separate function calls or as an array entered
function.

You can find out which cell(s) called the function using
Application.Caller, which returns either a single cell or the range that
the function was array-entered into.

The best way of implementing that type of function varies on what the
function does - what is your function doing?
 
Hi Dan,

Thanks for the input, I've tried this and can still not get it to work. I've
posted a new post with the example I'm trying to get working so this may
give more cluse as to my problem?

Thanks,

Jason
 
Jason

I'm not a VBA guru by any means, but I replied to your problem because I
thought there was a simple answer. Looking at your example code would help,
but I don't see the new post you mentioned below. Did you post it to this
news group - microsoft.public.excel.programming? I did a search on your
name and didn't come up with the new post....

Dan C.
 
Back
Top