PDA

View Full Version : Solved: Code works as Sub but not as a Function!



FhM
01-27-2010, 07:56 AM
I have wrote some code that basically works out the average of some data in a column and returns the answer. The function is used on a summary sheet and looks at the data on another worksheet which is filtered for certain criteria. When I run it as a sub it returns 8 records and the answer I was expecting. Soon as I make it into a function the filter seems to be ignored and it adds up all the cells that should have been filtered and and return all the records as the number found. I was passing parameters to most of it but I am currently hard coding the options in an attempt to trouble shoot it.
The macro even works if I am on the summary sheet tab and run it normally.
I even made a new function and ran the sub I know to work, inside it and it still messed up. Any ideas? This is Excel 2003. Thanks in advance.


Sub GetAPS()
Dim rnDataArea, filteredrange, rowCell As Range
Dim LastRow, LastCol, colSum, numPupils As Integer
Dim SheetName As String
SheetName = "Year 8"

With Sheets(SheetName)
LastRow = Sheets(SheetName).UsedRange.Rows.Count
Set rnDataArea = .Range(.Cells(1, 1), .Cells(LastRow, 37))
'set AutoFilter
rnDataArea.AutoFilter field:=34, Criteria1:="M"
'Return filtered column as a range
Set filteredrange = rnDataArea.Columns(2).Offset(1).SpecialCells(xlCellTypeVisible)

End With

'go through the cells in each selected Column
For Each rowCell In filteredrange.Cells '.Offset(1)

colSum = colSum + LevelToPoints(rowCell.Value)


'MsgBox filteredrange.Count


Next
numPupils = filteredrange.Count - 1
MsgBox colSum & vbCrLf & numPupils
colSum = colSum / numPupils
'reset the AutoFilter
rnDataArea.AutoFilter
MsgBox colSum
'GetAPS = colSum
End Sub

Ps LevelToPoints is a one of my functions that turns an alpha numeric grade into an integer.

mdmackillop
01-27-2010, 08:17 AM
Can you post a sample workbook?

FhM
01-27-2010, 08:29 AM
I will try and put together a dummy version as the current data is all confidential info. Trying the code again just now I will just say that everything works apart from the filter when it is made into a Function. I now have two identical sets of code but one is a sub and one a function so it is very strange.

FhM
01-27-2010, 09:13 AM
Ok I have a sanitised version with the same error. I have only included 15 names but I have noticed it is still returning the full number of the range of names that I have deleted.

To test run the macro GetAPS this should show a msgbox saying 7 pupils.

Then activate the formula on the summary sheet at C5 this will show 149 pupils!

I am sure it is just a little thing that is wrong but I'm not experienced enough to spot it.

Thanks in advance

Bob Phillips
01-27-2010, 12:05 PM
That is because a function cannot change a worksheet, so it cannot set the filter, so it counts all 150 rows, not just the the 7 (you think) you are filtering.

FhM
01-27-2010, 12:10 PM
That is because a function cannot change a worksheet, so it cannot set the filter, so it counts all 150 rows, not just the the 7 (you think) you are filtering.

Gah well that messes up everything then. Any suggestions on an alternative approach?

Bob Phillips
01-27-2010, 12:59 PM
Loop through all cells testing for thr filter criteria.

FhM
01-27-2010, 01:09 PM
Loop through all cells testing for thr filter criteria.

Ok thanks. I will do that, I just thought there might be a better way but I will stick with simple. :beerchug: