Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    specialcells not working inside UDF (2000sp4)

    Is it possible to determine the last cell in a sheet in a user defined function? Specialcells(xlCellTypeLastCell) returns A1 whilst the function is running (but the correct value once the function stops). I seem to remember that one can't use anything inside a function which changes the focus on a worksheet, but didn't think that the specialcells method did so.

    I'm basically trying to write a function which will return whether a value exists in a particular worksheet, so finding that last cell is quite important.

    I thought that this might be quite a common problem, but searching on specialcells and function yielded no useful hits.

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: specialcells not working inside UDF (2000sp4)

    Using SpecialCells(xlCellTypeLastCell) in a UDF makes my Excel 2002 crash.

    I don't quite understand why it would be important to find the last cell, but try

    Dim lngRow As Long
    Dim lngCol As Long
    Dim rngLast As Range
    lngRow = wsh.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    lngCol = wsh.Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    Set rngLast = wsh.Cells(lngRow, lngCol)

    where wsh is the worksheet you're working with. This returns the correct cell in a UDF.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: specialcells not working inside UDF (2000sp4)

    If you are trying to just loop thru all the cells and limit the range, you could just loop thru the cells of :
    Activesheet.Usedrange

    Steve

  4. #4
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: specialcells not working inside UDF (2000sp4)

    Thanks Hans,

    This fails in a UDF in Excel 2000. Teasing out the reason turns up that the find statement returns a range that is "nothing".

    Fortunately the UsedRange suggestion works fine. Unfortunately, the Find method still fails to work. The suggestion to use the CountIf worksheet function in another thread works for my current purposes - to find out whether an element exists in a sheet or not - but would be useless to manipulate that cell.

    Why doesn't Find work within a function?

    The reason, possibly misguied, that I was looking for the last cell, BTW, is that Find works quicker on smaller ranges, in my experience. Somewhat irrelevant now that it doesn't work at all!

  5. #5
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: specialcells not working inside UDF (2000sp4)

    Thanks

    Don't like looping - too slow, but the UsedRange property works perfectly, on my current worksheets. Thank you very much.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: specialcells not working inside UDF (2000sp4)

    I'm using Excel 2002 (XP), and Find works correctly in a user-defined function. Fortunately Steve has found a workaround for you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •