Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Blacktown, Sydney, New South Wales, Australia
    Posts
    175
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Determine if Value or Formula (XL2002)

    I currently have a need to identify if a cell contents are a value or a formula. I have written a VBA routine to clear the contens of a column of data. However, because the data is in about 15 or 16 blocks, with a total formula at the bottom of each block, I dont wish to clear the formulas.
    I could give each block a range name, but each month there is the possibility of additional blocks being inserted, and I would need to modify the code each time as well as add a new range name.
    I would like to be able to define one range and examine each cell in the range to determine if it contains a formula. If not, clear contents. I thought of looking for a '=' as the first character, but thought there are other characters such as '+' and '-' for which I would also need to test. This one range would automatically expand if I inserted a new block somewhere in the middle. Sequence of the blocks is not an issue.

    Any Ideas out there?

    TIA

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

    Re: Determine if Value or Formula (XL2002)

    You can look at the HasFormula property of each cell:

    Dim oCell As Range
    For Each oCell In Range("A1:K100")
    If oCell.HasFormula = False Then
    oCell.ClearContents
    End If
    Next oCell

    But it's more efficient to use SpecialCells:

    Range("A1:K100").SpecialCells(xlCellTypeConstants) .ClearContents

  3. #3
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Blacktown, Sydney, New South Wales, Australia
    Posts
    175
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Determine if Value or Formula (XL2002)

    Hmmm 3 minutes to reply after I spent 40. Gee I luv this website

    Thanks Hans

Posting Permissions

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