Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpecialCells in Worksheets Functions
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can these look ups be done using Worksheets functions | Excel Worksheet Functions | |||
Functions for all worksheets | Excel Discussion (Misc queries) | |||
Functions in worksheets | Excel Discussion (Misc queries) | |||
SpecialCells in Worksheets Functions | Excel Programming | |||
Functions across multiple worksheets | Excel Worksheet Functions |