Solved

Clearing cell if the neighboring cell is blank

Posted on 2011-09-29
5
444 Views
Last Modified: 2012-05-12
I've got a large workbook, that contans a number of worksheets which capture subsets of data from a much larger worksheet and insert rows for subtotals, blank spacer lines, and totals.  The code for doing this works great.

Several of these sheets require that I insert columns between several rows and establish a formula in those columns which computes the difference between the columns on either side of the new column.  I'm using the attached code to do this.

The problem is that this code inserts the formulas from the top cell to the bottom row, and disregards the "spacer" rows, resulting in zeros in those rows, instead of blanks.

In a previous question, I was advised to use the SpecialCells method to clear these values (see the remarked line) but I have been unable to get the syntax right to blank on the cells in the current column (intCol) where the value in the next cell (intCol + 1) is blank.

So, I tried modifying the formula I was putting in the cells, to use the IF() statement to test for a blank neighboring cell, and put a "" in the current cell if the neighbor is blank.  That part of the formula works, but if the neighboring cell contains a value, then the formumla I created actually results in the cell containing the text ('=RC[1]' or '=RC[1] - RC[-1]') showing up in the cell instead of the results of that formula.

What I'm looking for is the answer to how to do both of these techniques properly.


intCol = 5
    intRow = sht.Range("B4000").End(xlUp).Row
    
    While sht.Cells(5, intCol) <> ""
    
        sht.Columns(intCol).Insert Shift:=xlToRight
        sht.Cells(4, intCol) = sht.Cells(4, intCol + 1)
        sht.Cells(4, intCol).NumberFormat = "m/d/yy;@"
        sht.Cells(5, intCol + 1) = "YTD Balance"
        sht.Cells(5, intCol) = "Activity"
        
        If intCol = 5 Then
            sht.Cells(4, 5).Select
            Selection.Font.Bold = True
            Selection.HorizontalAlignment = xlCenter
            sht.Range(Cells(6, 5), Cells(intRow, 5)).FormulaR1C1 = "=RC[1]"
'            sht.Range(Cells(6, 5), Cells(intRow, 5)).FormulaR1C1 = "=if(RC[1]="""", """", ""=RC[1]"")"
        Else
            sht.Range(Cells(6, intCol), Cells(intRow, intCol)).FormulaR1C1 = "=RC[1] - RC[-1]"
'            sht.Range(Cells(6, intCol), Cells(intRow, intCol)).FormulaR1C1 = "=if(RC[1] = """", """", ""=RC[1] - RC[-1]"")"
        End If
        'sht.Range(Cells(6, intCol + 1), Cells(intRow, intCol + 1)).SpecialCells(xlCellTypeBlanks).Offset(, -11).ClearContents
        intCol = intCol + 2
    Wend

Open in new window

0
Comment
Question by:Dale Fye (Access MVP)
  • 3
  • 2
5 Comments
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
Comment Utility
Interesting - when I run your code, I get the correct formula.  I don't get "=RC[1]" or "=RC[1] - RC[-1]" showing up.  Perhaps you have set somehow Excel to Show Formulas?  Check the cell formatting in your actual workbook, as perhaps the formulas are pasting right but due to the formatting of the cells, the actual formula shows up rather than values.  E.g., a quick test, pull up another workbook where you see formulas converting to values properly and copy/paste formats on top of these formulas - do they then convert to values?  Then its a formatting issue.  Also check to see if you are showing formulas - Un the Formulas ribbon, under Formula Auditing, see the option to Show Formulas.  One of these must be the issue, I think - either format of the cells, or you have ShowFormulas on.

When you see the attached, run the macro button and see the formulas all look correct.

----------------------------------------------

I looked at your attempt to clear the originally "blank" rows and could not understand your rationale for .offset(,-11).  However, .Offset(,-1) actually works.  Could this have been a type-o?

Also, there's only need to get the blank rows and clear them, once, rather than do them in a loop.

Since you start inserting in column 5, I'm going to assume Column B is "pristine" (since you use it to determine last row) and blank rows can be used on this column.  You could change this to whatever column is appropriate to test for blank rows.  a result, we can add the command (after the Wend):

    'using column B, check for blank rows to ensure that all rows are "blanked" due to formula overwrites in above code
   sht.Range(Cells(6, 2), Cells(introw, 2)).SpecialCells(xlCellTypeBlanks).EntireRow.ClearContents

Here's the complete code:
Sub doWork()
    Set sht = ActiveSheet
    
    intCol = 5
    introw = sht.Range("B" & sht.Rows.Count).End(xlUp).Row
    
    While sht.Cells(5, intCol) <> ""
    
        sht.Columns(intCol).Insert Shift:=xlToRight
        sht.Cells(4, intCol) = sht.Cells(4, intCol + 1)
        sht.Cells(4, intCol).NumberFormat = "m/d/yy;@"
        sht.Cells(5, intCol + 1) = "YTD Balance"
        sht.Cells(5, intCol) = "Activity"
        
        If intCol = 5 Then
            sht.Cells(4, 5).Select
            Selection.Font.Bold = True
            Selection.HorizontalAlignment = xlCenter
            sht.Range(Cells(6, 5), Cells(introw, 5)).FormulaR1C1 = "=RC[1]"
'            sht.Range(Cells(6, 5), Cells(intRow, 5)).FormulaR1C1 = "=if(RC[1]="""", """", ""=RC[1]"")"
        Else
            sht.Range(Cells(6, intCol), Cells(introw, intCol)).FormulaR1C1 = "=RC[1] - RC[-1]"
'            sht.Range(Cells(6, intCol), Cells(intRow, intCol)).FormulaR1C1 = "=if(RC[1] = """", """", ""=RC[1] - RC[-1]"")"
        End If
        'corrected, below, but using code outside the while loop to affect this change, once
        'sht.Range(Cells(6, intCol + 1), Cells(intRow, intCol + 1)).SpecialCells(xlCellTypeBlanks).Offset(, -1).ClearContents
        intCol = intCol + 2
    Wend

    'using column B, check for blank rows to ensure that all rows are "blanked" due to formula overwrites in above code
    sht.Range(Cells(6, 2), Cells(introw, 2)).SpecialCells(xlCellTypeBlanks).EntireRow.ClearContents
End Sub

Open in new window


See attached dummy worksheet that demonstrates the code works.
Enjoy!

Dave
formulaInCols-r1.xls
0
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
Comment Utility
Dave,

The SpecialCells row is exactly what I was looking for, and it is even better since it is outside the loop.

When I use the spreadsheet you posted, everything works great, and the simple formula works great, displaying the actual value of RC[1] or RC[1]-RC[-1], as expected.

But when I rem out those two lines and unrem the second formula line in each part of the IF Then, what gets displayed in the cells is not the value, but the text "=RC[1]"  (without quotes) or "=RC[1] - RC[-1]", so it cannot be a formatting or display issue.

I've made the modifications discussed above and am posting your example back again.
formulaInCols-r1.xlsm
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Ahh. Will get back to you after a few hours as I have an upcoming dinner meeting
0
 
LVL 41

Assisted Solution

by:dlmille
dlmille earned 500 total points
Comment Utility
A few too many quotes and equals signs.

Here's your revised code:
Sub doWork()
    Set sht = ActiveSheet
    
    intCol = 5
    intRow = sht.Range("B" & sht.Rows.Count).End(xlUp).Row
    
    While sht.Cells(5, intCol) <> ""
    
        sht.Columns(intCol).Insert Shift:=xlToRight
        sht.Cells(4, intCol) = sht.Cells(4, intCol + 1)
        sht.Cells(4, intCol).NumberFormat = "m/d/yy;@"
        sht.Cells(5, intCol + 1) = "YTD Balance"
        sht.Cells(5, intCol) = "Activity"
        
        If intCol = 5 Then
            sht.Cells(4, 5).Select
            Selection.Font.Bold = True
            Selection.HorizontalAlignment = xlCenter
'            sht.Range(Cells(6, 5), Cells(introw, 5)).FormulaR1C1 = "=RC[1]"
            sht.Range(Cells(6, 5), Cells(intRow, 5)).FormulaR1C1 = "=if(RC[1]="""", """", RC[1])"
        Else
'            sht.Range(Cells(6, intCol), Cells(intRow, intCol)).FormulaR1C1 = "=RC[1] - RC[-1]"
            sht.Range(Cells(6, intCol), Cells(intRow, intCol)).FormulaR1C1 = "=if(RC[1] = """", """", RC[1] - RC[-1])"
        End If
        'corrected, below, but using code outside the while loop to affect this change, once
        'sht.Range(Cells(6, intCol + 1), Cells(intRow, intCol + 1)).SpecialCells(xlCellTypeBlanks).Offset(, -1).ClearContents
        intCol = intCol + 2
    Wend

    'using column B, check for blank rows to ensure that all rows are "blanked" due to formula overwrites in above code
'    sht.Range(Cells(6, 2), Cells(intRow, 2)).SpecialCells(xlCellTypeBlanks).EntireRow.ClearContents
End Sub

Open in new window


See attached.

Dave
formulaInCols-r1.xlsm
0
 
LVL 47

Author Closing Comment

by:Dale Fye (Access MVP)
Comment Utility
Thanks,  Dave.

One of these centuries I'll be as proficient in Excel as I am in Access.  And will still look to guys like you for the tough solutions.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Outlook Free & Paid Tools
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now