This code works in a VBA Sub but not in a VBA function
Dim wsPurchase As Worksheet
Set wsPurchase = Worksheets("Purchase")
Dim rCriteria As Range
Set rCriteria = wsPurchase.Range("A1:A2")
rCriteria.Offset(1, 0).Value = sSet

Is there a way to change a worksheet cell from within a VBA function?

No. A function can only return a value (replace its call). You cannot change
anything in a worksheet or Excel's settings from a function.

--
Kind regards,

Niek Otten

"excelman" wrote in message
...
This code works in a VBA Sub but not in a VBA function
Dim wsPurchase As Worksheet
Set wsPurchase = Worksheets("Purchase")
Dim rCriteria As Range
Set rCriteria = wsPurchase.Range("A1:A2")
rCriteria.Offset(1, 0).Value = sSet

Is there a way to change a worksheet cell from within a VBA function?

Not knowing what sSet was and assuming s meant string I put quotes around
sSet.

The code worked fine from both a Subroutine and Function, as was expected.
I'm using Excel 2002 SP3 under Win XP SP1.

--
My handle should tell you enough about me. I am not an MVP, expert, guru,
etc. but I do like to help.


"excelman" wrote in message
...
This code works in a VBA Sub but not in a VBA function
Dim wsPurchase As Worksheet
Set wsPurchase = Worksheets("Purchase")
Dim rCriteria As Range
Set rCriteria = wsPurchase.Range("A1:A2")
rCriteria.Offset(1, 0).Value = sSet

Is there a way to change a worksheet cell from within a VBA function?

Yes and your code should work the same way.... except if you are calling the
function from a cell ie you are using the function as a worksheet function
(in C4: =MyFunc(...) ) in which case changing the sheet/cells through the
function is not allowed.
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"excelman" wrote:

This code works in a VBA Sub but not in a VBA function
Dim wsPurchase As Worksheet
Set wsPurchase = Worksheets("Purchase")
Dim rCriteria As Range
Set rCriteria = wsPurchase.Range("A1:A2")
rCriteria.Offset(1, 0).Value = sSet

Is there a way to change a worksheet cell from within a VBA function?

< except if you are calling the function from a cell

A very useful addition! I tend to forget that and always assume functions
are called from a worksheet.

Thanks!

--
Kind regards,

Niek Otten

"sebastienm" wrote in message
...
Yes and your code should work the same way.... except if you are calling
the
function from a cell ie you are using the function as a worksheet function
(in C4: =MyFunc(...) ) in which case changing the sheet/cells through the
function is not allowed.
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"excelman" wrote:

This code works in a VBA Sub but not in a VBA function
Dim wsPurchase As Worksheet
Set wsPurchase = Worksheets("Purchase")
Dim rCriteria As Range
Set rCriteria = wsPurchase.Range("A1:A2")
rCriteria.Offset(1, 0).Value = sSet

Is there a way to change a worksheet cell from within a VBA function?

A function can only change the value in the cell from which it i
called.

There are several answers to what you want to do but a bit mor
information on exactly what would help

--
tony
-----------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...fo&userid=2107
View this thread: http://www.excelforum.com/showthread.php?threadid=51029

I am using the function as a worksheet function C4: =MyFunc(...)
I am trying to set the criteria dynamically each time the MyFunc(sSet) is
called
total = Application.WorksheetFunction.DSum(rDB, rColumn, rCriteria)

"tony h" wrote:


A function can only change the value in the cell from which it is
called.

There are several answers to what you want to do but a bit more
information on exactly what would help.


--
tony h
------------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
View this thread: http://www.excelforum.com/showthread...hreadid=510293