Results 1 to 13 of 13
  1. #1
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    cell.ClearContents (97sr2H)

    I should never go on vacation. I can't figure out why this macro only handles the first cell within the selection which contains a formula, and then quits:

    Private Sub clrblankvalformulas()
    Dim rngCell As Range
    For Each rngCell In Selection.Cells
    If rngCell.Value = "" Then rngCell.ClearContents
    Next rngCell
    End Sub

    My object is to clean out =IF(,,) formulas which evaluate to "".
    -John ... I float in liquid gardens
    UTC -7DS

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: cell.ClearContents (97sr2H)

    John - I just tried it and it worked fine for me. I cut & pasted the VBA from your post (other than removing "private" keyword). It cleared all the "" cells in the selected range.
    Is there something odd about your workbook or your IF statements? - do some of them yield "_" rather than ""?

    Could you post a copy of a s/sheet where the procedure isn't working?

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: cell.ClearContents (97sr2H)

    It works for me in Excel 2k.
    Legare Coleman

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: cell.ClearContents (97sr2H)

    Thanks for looking, Dean and Legare. The spreadsheet is not protected. The IF's are
    =IF(ISERROR(FIND("Total",A15)),"",N15/J15/12)
    The spreadsheet will have to be heavily censored before I can post it, so before I do that I'll try it on a fresh test one and see what happens.
    -John ... I float in liquid gardens
    UTC -7DS

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: cell.ClearContents (97sr2H)

    John,

    Is it possible that the formula might not be exactly the same in all cells, and instead of "", " " is being inserted where ISERROR is true.

    Or do you have a sheet event triggering that collapses the selection to the active cell. ?

    Andrew C

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: cell.ClearContents (97sr2H)

    Anything is possible, but I think the answers to your questions are no and no. The three columns of formulas vary a little, but are all in the form:
    =IF(ISERROR(FIND("Total",A41)),"",<do some simple math>)
    and all produce the desired result.

    I created a new test dummy of the actual, first time the code would run only on the first cell, after close and reopen it ran on all cells as desired. I had closed and reopened the actual WB a couple of times, without success. Must be something obvious I'm missing. Is there anything in VBE Tools, Options that might cause this? Error Trapping is set at "Break in Class Module".
    -John ... I float in liquid gardens
    UTC -7DS

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: cell.ClearContents resolution (97sr2H)

    I think I resolved this but it doesn't completely make sense to me ... <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

    Andrew, you were hinting at something that I missed; not a sheet event, but the following simple UDF at work in this same book:

    Private Function SUMBOLD(rngSumRange As Range) As Double
    Application.Volatile True
    Dim rngCell As Range
    SUMBOLD = 0
    For Each rngCell In rngSumRange
    If rngCell.HasFormula Then SUMBOLD = SUMBOLD + rngCell.Value
    Next rngCell
    End Function

    So first thing I did was to overwrite the UDF results with corresponding values and comment out the UDF. The macro still stopped prematurely, but now it errored out with a Type Mismatch on the column R formula. My problem appeared to be that of the three columns with If statements, the last column, Column R, was dependent on the first two:

    Column P: =IF(ISERROR(FIND("Total",A41)),"",N4/J4/12)
    Column Q: =IF(ISERROR(FIND("Total",A41)),"",O4/K4/12)
    Column R: =IF(ISERROR(FIND("Total",A41)),"",R4/Q4-1)

    As soon as a column P formula was wiped out, the column R formula would calculate to #DIV/0, and when the macro reached that cell it would error out with Type Mismatch.

    In order to overkill the problem I changed the macro to:

    Sub clrblnkvlfrmulas()
    Dim rngCell As Range
    For Each rngCell In Selection.Cells
    If (rngCell.HasFormula And IsError(rngCell.Value)) Or _
    rngCell.Value = "" Then rngCell.ClearContents
    Next rngCell
    End Sub

    AND I changed Column R to =IF(ISERROR(FIND("Total",A4)),"",IF(Q4,R4/Q4-1,))

    Now it works, but what I don't understand is why it would formerly just stop after the first cell (which happened to always be the Column P cell) with no error message. Why the first cell, not the first Column R cell inthe first selection row? Was the UDF's Application.Volatile True impacting the macro? Have I missed something obvious? Anybody got any insight? (Am I making any <img src=/w3timages/censored.gif alt=censored border=0> sense?)
    -John ... I float in liquid gardens
    UTC -7DS

  8. #8
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: cell.ClearContents resolution (97sr2H)

    Now at least I get the link between floating in liquid gardens and the photo:

    You're skiing down an iceberg! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Gre

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: cell.ClearContents resolution (97sr2H)

    Deep powder snow IS a liquid garden. No ice skiing for me, hurts to fall. And just to confound everyone, I'm about to make a seasonal change to my userpic. So, got any ideas on my weird problem in this thread?
    -John ... I float in liquid gardens
    UTC -7DS

  10. #10
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: cell.ClearContents resolution (97sr2H)

    John, you're talking to someone who learnt ice hockey before he started skiing! Seriously though, the long answer is much the same as the short one. The Application Volatile setting seems why the problem was hidden. You could, of course test this, if you wish. Glad the summer weather was still holding out Down Under.
    Gre

  11. #11
    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

    Re: cell.ClearContents resolution (97sr2H)

    Hi John,
    Was your SUMBOLD formula referencing column R? If so, I suspect it has something to do with the fact that your function was trying to add the value of a #DIV/0 cell to its running total.
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: cell.ClearContents resolution (97sr2H)

    It might help to set calc to manual at the start of your macro. That way results stay put until you switch it back to automatic. It would prevent intermediate (calculation) errors from stopping your macro from doing its thing.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  13. #13
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: cell.ClearContents resolution (97sr2H)

    To all, thanks for the ideas. Jan Karel, I will turn recalc off, dunno why I didn't think of it, but I didn't understand the problem until I worked through it as above. Rory, the UDF was working on predecesssor columns J & K and those calcs were not referenced in any way by column R. And thanks Unkamunka for the advice on Application.Volatile.

    Thanks, guys!
    -John ... I float in liquid gardens
    UTC -7DS

Posting Permissions

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