cell.ClearContents (97sr2H)

Topic

New Reply

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 “”.

Reply | Quote

Replies

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?

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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)),””,)
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”.

Reply | Quote

I think I resolved this but it doesn’t completely make sense to me …

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 sense?)

Reply | Quote

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

You’re skiing down an iceberg!

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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!

Reply | Quote

It works for me in Excel 2k.

Reply | Quote