MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

Problem with a Function

• Follow

```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
```
 0

```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

```
 0

```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

```
 0

```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
```
 0

```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
```
 0

4 Replies
180 Views

Similiar Articles:

7/20/2012 9:33:06 PM