Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am having a challenge with the criteria formula for an advanced filter.

    The file/range where the formula points to contains formulas which return values. My challenge is that data is being extracted for those cells containing formulas as well as numbers. I would only like to extract those cells that have values not formulas.

    Thanks for taking a look,
    John
    Attached Images Attached Images

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could create a user-defined function in a module in the Visual Basic Editor:

    Code:
    Function CellHasFormula(oCell As Range) As Boolean
      CellHasFormula = oCell.HasFormula
    End Function
    You can use this in formulas and expressions, e.g.

    =CellHasFormula(Sheet2!A3)

    This will return TRUE if Sheet2!A3 contains a formula, FALSE otherwise.


  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hans,

    I tried your suggestion and it returned a "#Value"; referencing another file. I then tried it within the same workbook; different sheet and received the #Value as well.

    Regards,
    John

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The user-defined function does work with cells in other sheets in the same workbook, so you must have done something wrong.

    It also works with cells in another workbook, but only if that workbook is open in Excel. If the other workbook is closed, CellHasFormula will return #VALUE.

  5. #5
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Might be worth sticking Application.Volatile into the Function.

    I have seen custom functions return a Value Error until a recalc takes place before.

    Code:
    Function CellHasFormula(oCell As Range) As Boolean
      Application.Volatile
      CellHasFormula = oCell.HasFormula
    End Function
    Andrew

Posting Permissions

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