Excel: VBA Calculation giving error on "blank" cell

Question

My VBA calculation on sheet "$$$" in column L gives a "Runtime error 13: Type mismatch" when I run it, using the "original calculate balance" button. This is confusing because this has worked for years without problem, until I made a change to the formula on sheet "Template" in column C to leave rows w/out data "blank" with "".

Spreadsheet here: XLS File

What I've determined is this: Blank cells created w/ the "" and copied to another sheet, are causing the runtime error when I perform a simple addition calculation with VBA. Excel is treating those "blank" cells as text and it is breaking the addition formula.

The steps to repeat the error follow:

1) The formula on sheet "Template", col C, results in "blank" cells w/ the "", as it is supposed to. This is automatic. The formula is:

  =IF(B4="","",IFERROR(INDEX($F$3:$F$8,MATCH(B4,$E$3:$E$8,0)),""))

2) On sheet "$$$", press the "Insert Template" button. This copies the range "Template_RecurringExp" from sheet "Template" and pastes it into sheet "$$$" in columns I-K after the last used row. I leave the blank rows in the template for ease in entering manual data in between the templated rows.

3) Press "Original Calculate Balance" on sheet "$$$". This will calculate the balance from the values in the rows above and values in column K. The VBA (found in module "SumBalanceOriginal") for this is:

    Option Explicit
Sub RunningBalO()
'creates a running balance column
'assumes starting bal is the first cell of bal col
'assumes +/- transactions are 1 column to the left of the bal col & 1 row below starting bal
Application.ScreenUpdating = False
Dim NumRows As Integer
Dim colDebit As Integer
Dim tempSum As Single
Dim i As Integer
Dim ws As Worksheet
Dim cStartBal As Range
Dim RowStart As Integer
Dim colBal As Integer
Dim coldebitltr As String

Set ws = ThisWorkbook.Sheets("$$$")
Set cStartBal = ws.Range("l4")                'L4

tempSum = cStartBal.Value               'starting balance for the PENDING transactions
colDebit = cStartBal.Column - 1
colBal = cStartBal.Column
RowStart = cStartBal.Row + 1              'First row with debits, used to set "i" in loop


'Find last used row in debit column
coldebitltr = Split(Cells(1, colDebit).Address, "$")(1)  'Converts the column # to a letter to pass to func LastRow
        'Debug.Print "col letter for debits= " & coldebitltr
NumRows = LastRow(coldebitltr, ws) + 1      'call func LastRow for "K" and Suntrust

        'Debug.Print "NumRows= " & NumRows
'Exit Sub
        'Debug.Print "col# for Debit= " & colDebit
       'Debug.Print "Rowstart= " & RowStart
'Calculate & fill running balance
With ws
    For i = RowStart To NumRows             '"i = " starting row of debits
          tempSum = tempSum - ws.Cells(i, colDebit).Value
            ' Debug.Print "sum = " & tempSum
            'Debug.Print "i = " & i
            ws.Cells(i, colBal).Value = Format(tempSum, "0.00")
       Next i
End With
Application.ScreenUpdating = True
End Sub

The runtime error occurs on the tempSum = tempSum - ws.Cells(i, colDebit).Value line.

All the courses of action I can think of are:

1) Find a way to get rid of the "blank" cell and turn it into an empty cell w/out the "". I've not yet found anything online to do this. There was something about using NA() instead of "", but it didn't look like it would work.

2) Convert the "blank" cells in column K into empty cells when pasting via the "InsertTemplate" macro executed by the "Insert Template" button. Not sure how to do that.

3) Edit the VBA formula to ignore the "blank" text cells.
I added this if statement before the calculation in module "SumBalanceRevised":

If IsNumeric(ws.Cells(i, colDebit).Value) Then tempSum = tempSum - ws.Cells(i, colDebit).Value

It is executed by the button called "Revised Calculate Balance". That results in the calculation skipping over the empty rows. It looks like the calculations are correct. I would prefer that it didn't leave empty cells in the Balance column and fill completely down the column, like in column M - the testing column. It is not apparent to me how to make that happen.

4) Add VBA to the "Calculate Balance" VBA to convert the "blank" cells in col K to either be empty cells or not text.

Any suggestions on how to make either of the COAs work or an alternative not listed is appreciated.

Answer 1

Moving this VBA line:

 ws.Cells(i, colBal).Value = Format(tempSum, "0.00")

outside of the IF/End IFresults in the column L in being filled in, down to the last used row.

So that solved the issue in COA 3.

Now on to trying to get the formatting correct.

Answer 2

Your problem is that you are trying to use zero-length strings or text-that-look-like-numbers in a mathematical equation. 6 = 6 - "" or 6 = 6 - "3" is just as wrong as 6 = 6 - "A".

Runtime error 13: Type mismatch

If you haven't manually changed the cell's default horizontal alignment, you will note that any text (whether it looks like a number or not) is left-aligned; true numbers and dates are right-aligned by default. You can fix your immediate problem with this.

tempSum = tempSum - Val(.Cells(i, colDebit).Value)

But there are a number of other problems. The most obvious is this.

ws.Cells(i, colBal).Value = Format(tempSum, "0.00")

This takes the nice clean number you just produced and converts it into text-that-look-like-a-number. You want to change the number format of the cell.

.Cells(i, colBal).NumberFormat = "0.00"

Now, your coding style is very different from my own, you might want to consider this all-at-once alternative.

Option Explicit

Sub RunningBalO()
    'creates a running balance column

    With ThisWorkbook.Sheets("$$$")

        'fix the last running balance
        With .Cells(.Rows.Count, "L").End(xlUp)

            .Value = Val(.Value2)
            .NumberFormat = "0.00"

        End With

        With .Range(.Cells(.Rows.Count, "L").End(xlUp).Offset(1, 0), _
                    .Cells(.Rows.Count, "K").End(xlUp).Offset(0, 1))

            .FormulaR1C1 = "=SUM(R[-1]C, -RC[-1])"
            .NumberFormat = "0.00"
            'optionally remove formulas
            '.Value = .Value2

        End With

    End With

End Sub