Results 1 to 6 of 6
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,423
    Thanks
    125
    Thanked 5 Times in 5 Posts
    I have tried to develop code that will zero values in column D, except text and formulas, but including values that have been added for eg = 25+1500+800-65

    All blank cells must be ignored

    see my code below

    Sub Clear_Values()
    Dim cell As Range
    For Each cell In Range("D120")
    If IsNumeric(cell.Value) And Not IsEmpty(cell) And cell.HasFormula = False Then cell = 0
    Next cell
    End Sub



    Your assistance will be most appreciated

    Regards

    Howard
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Howard,

    Unfortunately, anything starting with an = sign is a formula.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Assuming you have no off-sheet formula references, then:
    Code:
    Sub Clear_Values()
       Dim cell As Range, rngPrec As Range
       For Each cell In Range("D1:D20")
          If IsNumeric(cell.Value) And Not IsEmpty(cell) Then
             If cell.HasFormula Then
                On Error Resume Next
                Set rngPrec = cell.Precedents
                On Error GoTo 0
                If rngPrec Is Nothing Then cell = 0
             Else
                cell = 0
             End If
          End If
       Next cell
    End Sub
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Rory,

    Set rngPrec = cell.Precedents --- Cool!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Be nicer if you could use a simple
    Code:
    If cell.Precedents.Count = 0 Then
    rather than an error trap, but hey.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,423
    Thanks
    125
    Thanked 5 Times in 5 Posts
    Hi Rory

    Thanks for the help, much appreciated

    Regards

    Howard

Posting Permissions

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