I had posted earlier some formulas I am trying to reduce to functions
and didn't get any response.  I guess it was too hard or maybe can't
be done.

Anyway, I'm taking a small piece of it and trying things on my own. In
a cell I have the following function:

=QBRushYds(1, 1)

The first argument is week number corresponding to a sheet name and
the second arguement is the position of the player corresponding to a
cell on the sheet.  The function returns an error (#Value):

Here is the function definition:

Public Function QBRushYds(W, Q)
'  Calculates Points for Rushing Yards by a QB
'
Dim Yds As Integer


Select Case W
    Case 1:  Sheets("Week1").Activate
    Case 2:  Sheets("Week2").Activate
    Case 3:  Sheets("Week3").Activate
    Case 4:  Sheets("Week4").Activate
    Case 5:  Sheets("Week5").Activate
    Case 6:  Sheets("Week6").Activate
    Case 7:  Sheets("Week7").Activate
    Case 8:  Sheets("Week8").Activate
    Case 9:  Sheets("Week9").Activate
    Case 10:  Sheets("Week10").Activate
    Case 11:  Sheets("Week11").Activate
    Case 12:  Sheets("Week12").Activate
    Case 13:  Sheets("Week13").Activate
    Case 14:  Sheets("Week14").Activate
    Case 15:  Sheets("Week15").Activate
    Case 16:  Sheets("Week16").Activate
    Case Else:  Sheets("Week17").Activate
  End Select
  
  Select Case Q
    Case 1: ActiveSheet.Range("C3").Select
    Case 2: ActiveSheet.Range("C4").Select
    Case 3: ActiveSheet.Range("C5").Select
    Case 4: ActiveSheet.Range("C6").Select
  End Select
  
  Yds = cell.Value
  QBRushYds = 0
  QBRushYds = Yds / 10
   
End Function

What's the problem?

Thanks.

          ////
         (o o)
-oOO--(_)--OOo-

Two boll weevils grew up in South Carolina.
One went to Hollywood and became a famous actor. 
The other stayed behind in the cotton fields and never amounted to much.

The second one, naturally, became known as the lesser of two weevils.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Remove nospam to email me.

Steve

You can not change anything at all in worksheets (or the rest of the Excel 
environment) from a UDF. This includes selecting cells or sheets.


-- 
Kind regards,

Niek Otten
Microsoft MVP - Excel

"SkippyPB" <swiegand@Nospam.neo.rr.com> wrote in message 
news:6u77j5l87hfum1k4tkv02q4sgnhm7nidlb@4ax.com...
>I had posted earlier some formulas I am trying to reduce to functions
> and didn't get any response.  I guess it was too hard or maybe can't
> be done.
>
> Anyway, I'm taking a small piece of it and trying things on my own. In
> a cell I have the following function:
>
> =QBRushYds(1, 1)
>
> The first argument is week number corresponding to a sheet name and
> the second arguement is the position of the player corresponding to a
> cell on the sheet.  The function returns an error (#Value):
>
> Here is the function definition:
>
> Public Function QBRushYds(W, Q)
> '  Calculates Points for Rushing Yards by a QB
> '
> Dim Yds As Integer
>
>
> Select Case W
>    Case 1:  Sheets("Week1").Activate
>    Case 2:  Sheets("Week2").Activate
>    Case 3:  Sheets("Week3").Activate
>    Case 4:  Sheets("Week4").Activate
>    Case 5:  Sheets("Week5").Activate
>    Case 6:  Sheets("Week6").Activate
>    Case 7:  Sheets("Week7").Activate
>    Case 8:  Sheets("Week8").Activate
>    Case 9:  Sheets("Week9").Activate
>    Case 10:  Sheets("Week10").Activate
>    Case 11:  Sheets("Week11").Activate
>    Case 12:  Sheets("Week12").Activate
>    Case 13:  Sheets("Week13").Activate
>    Case 14:  Sheets("Week14").Activate
>    Case 15:  Sheets("Week15").Activate
>    Case 16:  Sheets("Week16").Activate
>    Case Else:  Sheets("Week17").Activate
>  End Select
>
>  Select Case Q
>    Case 1: ActiveSheet.Range("C3").Select
>    Case 2: ActiveSheet.Range("C4").Select
>    Case 3: ActiveSheet.Range("C5").Select
>    Case 4: ActiveSheet.Range("C6").Select
>  End Select
>
>  Yds = cell.Value
>  QBRushYds = 0
>  QBRushYds = Yds / 10
>
> End Function
>
> What's the problem?
>
> Thanks.
>
>          ////
>         (o o)
> -oOO--(_)--OOo-
>
> Two boll weevils grew up in South Carolina.
> One went to Hollywood and became a famous actor.
> The other stayed behind in the cotton fields and never amounted to much.
>
> The second one, naturally, became known as the lesser of two weevils.
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
> Remove nospam to email me.
>
> Steve 

Functions on Excel worksheets can take no action other than to return a 
value to the cell they are in... UDFs are bound by the same rule, so you 
can't go around activating other worksheets. However, you don't need to 
activate a worksheet nor select a cell on a worksheet in order to get a 
value from a cell on a different worksheet... just specify the sheet and 
cell. This UDF should work fine for you (note I have reduced your code 
somewhat based on the regular naming system you used for the worksheet 
names)...

Public Function QBRushYds(W, Q)
  QBRushYds = Worksheets("Week" & W).Range("C2").Offset(Q).Value / 10
End Function

-- 
Rick (MVP - Excel)


"SkippyPB" <swiegand@Nospam.neo.rr.com> wrote in message 
news:6u77j5l87hfum1k4tkv02q4sgnhm7nidlb@4ax.com...
>I had posted earlier some formulas I am trying to reduce to functions
> and didn't get any response.  I guess it was too hard or maybe can't
> be done.
>
> Anyway, I'm taking a small piece of it and trying things on my own. In
> a cell I have the following function:
>
> =QBRushYds(1, 1)
>
> The first argument is week number corresponding to a sheet name and
> the second arguement is the position of the player corresponding to a
> cell on the sheet.  The function returns an error (#Value):
>
> Here is the function definition:
>
> Public Function QBRushYds(W, Q)
> '  Calculates Points for Rushing Yards by a QB
> '
> Dim Yds As Integer
>
>
> Select Case W
>    Case 1:  Sheets("Week1").Activate
>    Case 2:  Sheets("Week2").Activate
>    Case 3:  Sheets("Week3").Activate
>    Case 4:  Sheets("Week4").Activate
>    Case 5:  Sheets("Week5").Activate
>    Case 6:  Sheets("Week6").Activate
>    Case 7:  Sheets("Week7").Activate
>    Case 8:  Sheets("Week8").Activate
>    Case 9:  Sheets("Week9").Activate
>    Case 10:  Sheets("Week10").Activate
>    Case 11:  Sheets("Week11").Activate
>    Case 12:  Sheets("Week12").Activate
>    Case 13:  Sheets("Week13").Activate
>    Case 14:  Sheets("Week14").Activate
>    Case 15:  Sheets("Week15").Activate
>    Case 16:  Sheets("Week16").Activate
>    Case Else:  Sheets("Week17").Activate
>  End Select
>
>  Select Case Q
>    Case 1: ActiveSheet.Range("C3").Select
>    Case 2: ActiveSheet.Range("C4").Select
>    Case 3: ActiveSheet.Range("C5").Select
>    Case 4: ActiveSheet.Range("C6").Select
>  End Select
>
>  Yds = cell.Value
>  QBRushYds = 0
>  QBRushYds = Yds / 10
>
> End Function
>
> What's the problem?
>
> Thanks.
>
>          ////
>         (o o)
> -oOO--(_)--OOo-
>
> Two boll weevils grew up in South Carolina.
> One went to Hollywood and became a famous actor.
> The other stayed behind in the cotton fields and never amounted to much.
>
> The second one, naturally, became known as the lesser of two weevils.
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
> Remove nospam to email me.
>
> Steve 

On Thu, 24 Dec 2009 13:34:36 -0500, "Rick Rothstein"
<rick.newsNO.SPAM@NO.SPAMverizon.net> wrote:

>Functions on Excel worksheets can take no action other than to return a 
>value to the cell they are in... UDFs are bound by the same rule, so you 
>can't go around activating other worksheets. However, you don't need to 
>activate a worksheet nor select a cell on a worksheet in order to get a 
>value from a cell on a different worksheet... just specify the sheet and 
>cell. This UDF should work fine for you (note I have reduced your code 
>somewhat based on the regular naming system you used for the worksheet 
>names)...
>
>Public Function QBRushYds(W, Q)
>  QBRushYds = Worksheets("Week" & W).Range("C2").Offset(Q).Value / 10
>End Function

Thank you for your response.  I deleted all my code in the function
and replaced it with yours but I got the same result.  I get a #VALUE
error (a value used in the formula is of the wrong data type)

In my testing I have 200 in cell "Week1!$C3".  The I checked the
format of that cell and once I changed it to number 0 decimals, the
function worked fine.

Thanks for the help. This should get me going to changing my formulas
to add functions which can be modified much easier than a whole bunch
of fomulas.  

Happy Holidays.
          ////
         (o o)
-oOO--(_)--OOo-

Two boll weevils grew up in South Carolina.
One went to Hollywood and became a famous actor. 
The other stayed behind in the cotton fields and never amounted to much.

The second one, naturally, became known as the lesser of two weevils.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Remove nospam to email me.

Steve

On Thu, 24 Dec 2009 13:34:36 -0500, "Rick Rothstein"
<rick.newsNO.SPAM@NO.SPAMverizon.net> wrote:

>Functions on Excel worksheets can take no action other than to return a 
>value to the cell they are in... UDFs are bound by the same rule, so you 
>can't go around activating other worksheets. However, you don't need to 
>activate a worksheet nor select a cell on a worksheet in order to get a 
>value from a cell on a different worksheet... just specify the sheet and 
>cell. This UDF should work fine for you (note I have reduced your code 
>somewhat based on the regular naming system you used for the worksheet 
>names)...
>
>Public Function QBRushYds(W, Q)
>  QBRushYds = Worksheets("Week" & W).Range("C2").Offset(Q).Value / 10
>End Function

I have used the above as a template and have written around 27
different functions to address my needs however, I've come across one
thing I do in a formula that I don't know or haven't figured out how
to make it work in a function similar to the above with the same
arguments. 

The formula looks like this:

IF(AND(299<Week1!$H3,Week1!$H3<350),4,0))+
(IF(AND(349<Week1!$H3,Week1!$H3<400),6,0))+
(IF(AND(399<Week1!$H3,Week1!$H3<450),8,0))+
(IF(AND(449<Week1!$H3,Week1!$H3<500),10,0))+
(IF(Week1!$H3>499,12,0)

Simply put, it looks at the value in Worksheet named Week1 - cell H3
(could be H4, H5, H6 depending, also could be Week2 - 17) ) and if
that number falls within the listed values, it awards points.

Any help putting that into a function that will work with the Week and
Offset shown above would be greatly appreciated.

          ////
         (o o)
-oOO--(_)--OOo-

Two boll weevils grew up in South Carolina.
One went to Hollywood and became a famous actor. 
The other stayed behind in the cotton fields and never amounted to much.

The second one, naturally, became known as the lesser of two weevils.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Remove nospam to email me.

Steve