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' or '=RC - 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.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
Selection.Font.Bold = True
Selection.HorizontalAlignment = xlCenter
sht.Range(Cells(6, 5), Cells(intRow, 5)).FormulaR1C1 = "=RC"
' sht.Range(Cells(6, 5), Cells(intRow, 5)).FormulaR1C1 = "=if(RC="""", """", ""=RC"")"
sht.Range(Cells(6, intCol), Cells(intRow, intCol)).FormulaR1C1 = "=RC - RC[-1]"
' sht.Range(Cells(6, intCol), Cells(intRow, intCol)).FormulaR1C1 = "=if(RC = """", """", ""=RC - RC[-1]"")"
'sht.Range(Cells(6, intCol + 1), Cells(intRow, intCol + 1)).SpecialCells(xlCellTypeBlanks).Offset(, -11).ClearContents
intCol = intCol + 2