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