Problem with a Function

[SkippyPB]

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

 

[Niek Otten]

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.

 

[Rick Rothstein]

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

 

[SkippyPB]

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

 

[SkippyPB]

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