G
Guest
Does SpecialCells function properly when used in a custom worksheet function?
I cannot get it to work. Here is an example of a custom worksheet function
that I hope to build.
Public Function EvaluateRange(SearchRange As Range) As Integer
Dim RangeWithContent As Range
Dim Cell As Range
Dim Total As Integer
' this line always returns the entire range
Set RangeWithContent = SearchRange.SpecialCells(xlCellTypeConstants)
For Each Cell In RangeWithContent
' example logic
Select Case Cell.Value
Case "A"
Total = Total + 7
Case "B"
Total = Total + 11
Case "C"
Total = Total + 15
End Select
Next Cell
EvaluateRange = Total
End Function
In short, I am hoping to use SpecialCells to extract only the Cells with
content from the SearchRange. I need to cycle through all of the Cells with
content and perform logic on them to return a value.
I would expect SpecialCells to return only cells with a constant, but it
returns the entire range, regardless of the range argument used in the
worksheet. I have tried large and small ranges, but SpecialCells always
returns the entire SearchRange for RangeWithContent.
If SpecialCells will not work in this case, is there another efficent way to
eliminate empty cells from a range (that will work with a custom worksheet
function?) While the above example uses a relatively simple Select Case
structure to calculate a value, the actual function I am building requires
considerably more processor intensive logic (it involves looking up values
for each cell in a database), and the worksheet function is used muliple
times, so calculation time becomes an issue.
The SearchRange may be several thousand cells, so checking each one for a
value (even using "If isEmpty(Cell)" to exclude individual cells) takes a
good deal of processing time.
If I could skip evaluating the empty cells, it would help execution
tremendously.
I cannot get it to work. Here is an example of a custom worksheet function
that I hope to build.
Public Function EvaluateRange(SearchRange As Range) As Integer
Dim RangeWithContent As Range
Dim Cell As Range
Dim Total As Integer
' this line always returns the entire range
Set RangeWithContent = SearchRange.SpecialCells(xlCellTypeConstants)
For Each Cell In RangeWithContent
' example logic
Select Case Cell.Value
Case "A"
Total = Total + 7
Case "B"
Total = Total + 11
Case "C"
Total = Total + 15
End Select
Next Cell
EvaluateRange = Total
End Function
In short, I am hoping to use SpecialCells to extract only the Cells with
content from the SearchRange. I need to cycle through all of the Cells with
content and perform logic on them to return a value.
I would expect SpecialCells to return only cells with a constant, but it
returns the entire range, regardless of the range argument used in the
worksheet. I have tried large and small ranges, but SpecialCells always
returns the entire SearchRange for RangeWithContent.
If SpecialCells will not work in this case, is there another efficent way to
eliminate empty cells from a range (that will work with a custom worksheet
function?) While the above example uses a relatively simple Select Case
structure to calculate a value, the actual function I am building requires
considerably more processor intensive logic (it involves looking up values
for each cell in a database), and the worksheet function is used muliple
times, so calculation time becomes an issue.
The SearchRange may be several thousand cells, so checking each one for a
value (even using "If isEmpty(Cell)" to exclude individual cells) takes a
good deal of processing time.
If I could skip evaluating the empty cells, it would help execution
tremendously.