Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get a UDF to ignore all sheets but its own?
I'm having problems with the macro below.
I have several sheets with data formatted the same way. I have the UDF below in each of these sheets. When I switch sheets, the function does not update. I then press F9 to calculate, and the cell in all sheets is updated to include the value for the active sheet. I then change sheets, and notice the function contains the data for the last sheet I was on. So I press F9 again.. I would like the function to report only the values for the sheet it is on. Can I do that? Function CountStunts(Optional AssignedOrNot As Boolean = True) Application.Volatile Application.EnableEvents = False Dim mySheet As Worksheet Set mySheet = ActiveSheet Dim CountAssigned As Integer, CountUnassigned As Integer, tempStunts As Integer Dim mycelladdress As String Dim myCell As Range, StuntRange As Range CountStunts = 0: CountAssigned = 0: CountUnassigned = 0 Set StuntRange = mySheet.Range("$H$20:$H$23,$H$25:$H$27,$H$29:$H$31 ,$H$33:$H$35,$H$37:$H$39,$H$41:$H$43,$H$45:$H$46,$ H$48:$H$49,$H$51:$H$52,$H$54:$H$55,$H$57:$H$58,$H$ 60:$H$61,$H$63:$H$64,$H$66:$H$67,$H$69,$H$71,$H$73 ,$H$75") Set StuntRange = Application.Union(StuntRange, mySheet.Range("$P$20:$P$23,$P$25:$P$27,$P$29:$P$31 ,$P$33:$P$35,$P$37:$P$39,$P$41:$P$43,$P$45:$P$46,$ P$48:$P$49,$P$51:$P$52,$P$54:$P$55,$P$57:$P$58,$P$ 60:$P$61,$P$63:$P$64,$P$66:$P$67,$P$69,$P$71,$P$73 ,$P$75")) For Each myCell In StuntRange mycelladdress = myCell.Address If myCell < "" Then tempStunts = Len(myCell.Text) If myCell.Offset(0, -5).Value < "" Then CountAssigned = CountAssigned + tempStunts Else CountUnassigned = CountUnassigned + tempStunts End If End If Next myCell CountStunts = CountUnassigned If AssignedOrNot Then CountStunts = CountAssigned Application.EnableEvents = True End Function Thanks :) Darren |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get a UDF to ignore all sheets but its own?
Change
Set mySheet = Activesheet to Set mySheet = Application.Caller.Parent -- HTH Bob Phillips "Darren Hill" wrote in message news:opsqgbgld6ed89cl@omega... I'm having problems with the macro below. I have several sheets with data formatted the same way. I have the UDF below in each of these sheets. When I switch sheets, the function does not update. I then press F9 to calculate, and the cell in all sheets is updated to include the value for the active sheet. I then change sheets, and notice the function contains the data for the last sheet I was on. So I press F9 again.. I would like the function to report only the values for the sheet it is on. Can I do that? Function CountStunts(Optional AssignedOrNot As Boolean = True) Application.Volatile Application.EnableEvents = False Dim mySheet As Worksheet Set mySheet = ActiveSheet Dim CountAssigned As Integer, CountUnassigned As Integer, tempStunts As Integer Dim mycelladdress As String Dim myCell As Range, StuntRange As Range CountStunts = 0: CountAssigned = 0: CountUnassigned = 0 Set StuntRange = mySheet.Range("$H$20:$H$23,$H$25:$H$27,$H$29:$H$31 ,$H$33:$H$35,$H$37:$H$39,$ H$41:$H$43,$H$45:$H$46,$H$48:$H$49,$H$51:$H$52,$H$ 54:$H$55,$H$57:$H$58,$H$60 :$H$61,$H$63:$H$64,$H$66:$H$67,$H$69,$H$71,$H$73,$ H$75") Set StuntRange = Application.Union(StuntRange, mySheet.Range("$P$20:$P$23,$P$25:$P$27,$P$29:$P$31 ,$P$33:$P$35,$P$37:$P$39,$ P$41:$P$43,$P$45:$P$46,$P$48:$P$49,$P$51:$P$52,$P$ 54:$P$55,$P$57:$P$58,$P$60 :$P$61,$P$63:$P$64,$P$66:$P$67,$P$69,$P$71,$P$73,$ P$75")) For Each myCell In StuntRange mycelladdress = myCell.Address If myCell < "" Then tempStunts = Len(myCell.Text) If myCell.Offset(0, -5).Value < "" Then CountAssigned = CountAssigned + tempStunts Else CountUnassigned = CountUnassigned + tempStunts End If End If Next myCell CountStunts = CountUnassigned If AssignedOrNot Then CountStunts = CountAssigned Application.EnableEvents = True End Function Thanks :) Darren |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get a UDF to ignore all sheets but its own?
Thanks, that does the trick perfectly. Thanks again :) Darren On Sun, 8 May 2005 11:54:55 +0100, Bob Phillips wrote: Change Set mySheet = Activesheet to Set mySheet = Application.Caller.Parent -- Using Opera's revolutionary e-mail client: http://www.opera.com/m2/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ignore | Excel Discussion (Misc queries) | |||
ignore #n/a | Excel Discussion (Misc queries) | |||
Average calcs - ignore sheets | Excel Worksheet Functions | |||
IF to ignore #N/A | Excel Discussion (Misc queries) | |||
please ignore | Excel Worksheet Functions |